寻找订单相同的客户

问题描述

我需要找到下过相同订单的客户.(使用 T-SQL)

I need to find customers who have made identical orders. (Using T-SQL)

订单

OrderID Customerer  
1   2   
2   5   
3   6   
4   2   
5   4   
6   6   
7   8

订单行

OrderLineID OrderID OrderDate   OrderType   Quantity    Reference  
1   1   01/01/2011  1   1   Coca Cola  
2   1   01/01/2011  1   3   Tea  
3   2   02/02/2011  2   1   Coffee  
4   2   02/02/2011  2   2   Solo  
5   2   03/02/2011  1   1   Soda  
6   3   03/02/2011  1   3   Tea  
7   3   03/02/2011  1   1   Coca Cola  
8   4   05/06/2011  1   1   Beer  
9   5   06/06/2011  2   1   Tea  
10  5   06/06/2011  2   1   Coca Cola  
11  6   07/07/2011  1   1   Coffee  
12  6   07/07/2011  1   2   Solo  
13  6   07/07/2011  1   1   Soda  
14  6   07/07/2011  1   1   Beer  
15  7   08/08/2011  1   1   Beer  

此处订单 ID 为 1 和 3 的订单被视为相同，因为订单行的编号、数量"和参考"在两个订单上是相同的.这意味着客户 2 和 6 下了相同的订单.

Here orders with OrderID 1 and 3 are considered to be identical because the number for orderlines, "Quantity" and "Reference" are identical on both orders. Meaning that customer 2 and 6 have placed identical orders.

订单 5 与订单 1 和订单 3 不同，因为数量不同.

Order 5 are not identical to order 1 and 3 because Quantity differ.

订单 2 与订单 6 不同，因为订单行不同.

Order 2 are not identical to order 6 because orderlines differ.

订单 4 和 7 也相同.

Order 4 and 7 are also identical.

我正在寻找这样的结果:

I am searching for a ressult like this:

相同的订单

OrderID CustomeerID  
1   2  
3   6  
4   2  
7   8  

这似乎是一项简单的任务，但我就是不知道从哪里开始.(我还是 t-sql 的新手 :-) )

It seems like an easy task, but I just can't understand where to start.(I am still new to t-sql :-) )

推荐答案

这是 Martin 第二个建议的延伸.这将显示所有匹配的组合，没有任何重复.

This is an extension of Martin's second suggestion. This will show all matching combinations without any repetitions.

;With FmtOL(customer, orderid, complete_order) as
(            
SELECT  customer, orderid, complete_order
     FROM    Order O
                    cross apply ( SELECT    CAST(Quantity AS VARCHAR(30))
                                            + '~' + Reference + '~~'
                                  FROM      OrderLine OL
                                  WHERE     OL.OrderID = O.OrderID
                                  ORDER BY  Reference ,
                                            Quantity
                                FOR
                                  XML PATH('')
                                ) T ( complete_order )
)
SELECT  T1.OrderId, 
        T1.Customer,
        STUFF(C1.a, 1, 2, '') as [SameAs]
FROM    FmtOL T1
Cross apply ( SELECT '; ' + 'Customer ' + Cast(T2.Customer as varchar(30)) 
                  + '''s order ' + Cast(T2.OrderID as varchar(30))
                 FROM   FmtOL T2
                 WHERE  T1.Customer < T2.Customer
                   AND T1.OrderId < T2.OrderId
                   AND T1.complete_order = T2.complete_order
                 order by ';' + Cast(T2.Customer as varchar(30)) 
                    + '''s order ' + Cast(T2.OrderID as varchar(30))
                    , t2.orderid
                 for xml path('')
             ) C1 (a)
  where C1.a is not null

结果应如下所示:

OrderId Customer SameAs 
1       2        Customer 6's order 3 
4       2        Customer 8's order 7

这篇关于寻找订单相同的客户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！