如何使用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屋!