如何使用Microsoft SQL Server 2008连接具有某些特定条件的两个表？ [英] How to join two table with some specific condition using Microsoft SQL server 2008 ?

问题描述

我正在使用Microsfot SQL SERVER 2008



i有两张桌子..



客户表和 customer_trasaction 表



客户表有一个主键 customer_id



customer_transaction 表有一个外键 customer_id 。



客户完成的所有交易，添加在客户交易表中。



客户表数据

I am using Microsfot SQL SERVER 2008

i have two table..

customer table and customer_trasaction table

customer table has a primary key customer_id

customer_transaction table has a foreign key customer_id.

all the transaction done by customer , added in the customer transaction table.

customer table data

customer_id	Customer_Name	Address		        contact
32552		Mahesh pattn    New delhi, india	9090909090
32553		Raj kumar 	New delhi, india	9923992939

客户交易表结构

customer transaction table structure

Customer_Id	Ticket		Trasactionn_Name	Transaction_date	
1	32552		6140332552	ORDER CREATED		2016-03-14 18:04:57.027	
45	32552		6140332552	PART NOT AVAILABLE	2016-03-15 09:37:53.217	
1544	32552		6140332552	IN-TRANSIT TO FSL		2016-03-23 00:19:59.613	
2045	32552		6140332552	IN-TRANSIT TO CUSTOMER	2016-03-28 12:15:01.743	
3288	32552		6140332552	IN-TRANSIT TO CUSTOMER	2016-03-29 11:23:56.277		

现在我的查询是..

now my query is..

select cust.customer_id, cust.CustomerName , cust.TicketNo, trans.Transaction_name, trans.transaction_date
from mstCustomer cust inner join Transaction_details trans
where cust.Customer_id=trans.customer_id

i希望在报告中显示有关交易信息的客户详细信息..

现在在交易表中，交易可能是重复的...但是我们必须找到当前交易和之前的交易日期..



且条件是当前和之前的交易不应该相同..



现在我想显示这样的输出..

i want to show the customer details in report with transaction info..
now in the transaction table, the trasaction may be repeatative.. but we have to find the current transaction and the previous transaction with date..

and the condition is current and previous transaction should not be same..

now i want to show the output like this..

Customer_id	Customer_Name	Current_Trasaction		Current_Transaction_date	Previous_Transaction	Previous_Transaction_Date
32552		Mahesh pattnayak	IN-TRANSIT TO CUSTOMER	2016-03-29 11:23:56.277	IN-TRANSIT TO FSL		2016-03-23 00:19:59.613

如何获得此输出？



谢谢提前..

How to get this output ??

thanks in advance..

推荐答案

它有点复杂，但一般原则如下。注意：此代码仅供参考，因为我没有您的数据库进行测试，而且缺少您需要的其他信息。



首先，它更容易创建下面的视图显示如何将数据导入不同的列。需要根据需要更改其他数据和过滤器。



例如

Its a bit complicated but the general principle is below. Note: This code is a sample only as I do not have your database to test against and it is missing additional information you need.

First off it is easier to create a view below to show how to get the data into different columns. it will need to be changed to include additional data and filters as needed.

eg

SELECT top 2 
case when row_number() over(order by Transactiondate) = 1 then Transaction else '' end as [Current],  
case when row_number() over(order by Transactiondate) = 2 then Transaction else '' end as [Previous],  
row_number() over(order by eventdate) as row
FROM customer_transaction

然后将文本字段（一个将是''，一个将具有数据）组合成一行。



例如

Then combine the text fields (one will be '' and one will have the data) into a single row.

eg

SELECT STUFF((SELECT ',' + [Current] FROM View1 FOR XML PATH('')) ,1,1,'') AS [Current], STUFF((SELECT ',' + [Previous] FROM View1 FOR XML PATH('')) ,1,1,'') AS [Previous]

一旦你整理了这两个，他们就可以组合成一个大的陈述。



一篇好文章第二部分是



<啊ref =http://www.sqlmatters.com/Articles/Converting%20row%20values%20in%20a%20table%20to%20a%20single%20concatenated%20string.aspx>将表中的行值转换为单个连接string - SQLMatters [ ^ ]

你对当前交易的意思是什么？这是今天的交易吗？以前的交易是从开始到昨天的所有交易吗？

what do you mean with the current transaction? is it the transaction for today ? and previous transaction is all transactions from the start until yesterday?

这篇关于如何使用Microsoft SQL Server 2008连接具有某些特定条件的两个表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！