sql-server - 查找具有相同订单的客户

Question

我需要找到下过相同订单的客户。 (使用 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  

此处 OrderID 为 1 和 3 的订单被认为是相同的，因为订单行的编号、“数量”和“引用”在两个订单上是相同的。这意味着客户 2 和 6 下了相同的订单。

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

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

订单 4 和订单 7 也相同。

我正在搜索这样的结果:

相同的订单

OrderID CustomeerID  
1   2  
3   6  
4   2  
7   8  

这似乎是一件容易的事，但我就是不知道从哪里开始。(我还是 t-sql 的新手 :-) )

Answer 1

这是一种方法。

SELECT  O1.OrderID ,
        O1.Customer ,
        O2.OrderID ,
        O2.Customer
FROM    [Order] O1
        JOIN [Order] O2 ON O1.OrderID < O2.OrderID
                           AND O1.Customer <> O2.Customer
WHERE   NOT EXISTS ( SELECT Quantity ,
                            Reference
                     FROM   OrderLine
                     WHERE  O1.OrderID = OrderLine.OrderID
                     EXCEPT
                     SELECT Quantity ,
                            Reference
                     FROM   OrderLine
                     WHERE  O2.OrderID = OrderLine.OrderID )
        AND NOT EXISTS ( SELECT Quantity ,
                                Reference
                         FROM   OrderLine
                         WHERE  O2.OrderID = OrderLine.OrderID
                         EXCEPT
                         SELECT Quantity ,
                                Reference
                         FROM   OrderLine
                         WHERE  O1.OrderID = OrderLine.OrderID )

你也可以使用XML PATH模拟GROUP_CONCAT然后JOIN两个结果集

DECLARE @T TABLE
    (
      OrderId INT PRIMARY KEY,
      Customer INT ,
      complete_order VARCHAR(MAX)
    )  

    INSERT  INTO @T
            SELECT  *
            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
FROM    @T T1
WHERE   EXISTS ( SELECT *
                 FROM   @T T2
                 WHERE  T1.Customer <> T2.Customer
                        AND T1.OrderId <> T2.OrderId
                        AND T1.complete_order = T2.complete_order )