SQL 将 STUFF 函数添加到此查询中

Question

我有以下查询

SELECT TOP 1000 
    o.BuyerEMail,
    COUNT(*) HowMany,
    o.Name, o2.OrderID
FROM 
    Orders o
JOIN 
    (SELECT 
         BuyerEmail, MAX(OrderDate) Latest 
     FROM Orders 
     GROUP BY BuyerEmail) l ON o.BuyerEmail = l.BuyerEmail
JOIN 
    Orders o2 ON l.BuyerEmail = o2.BuyerEmail
              AND l.OrderDate = o2.OrderDate
WHERE 
    Pay != 'PayPal'
GROUP BY 
    o.BuyerEmail, o.Name, l.Latest
ORDER BY 

它只是生成一份关于客户以及他们与我们的订单数量以及最新订单的报告。

我要添加的是行表中最后一个订单的产品列表。

行表通过 OrderID 链接到订单表.

我正在寻找类似的东西:

JOIN
    (SELECT 
         OrderID,
         STUFF((SELECT ', ' + li.Code
                FROM tblLines li
                WHERE li.OrderID = o2.OrderID 
                FOR XML PATH ('')), 1, 1, '') AS [Codes]
GROUP BY
    OrderID, Code

所以最终表显示为

BuyerEmail | HowMany | Name | Latest | Codes
---------------------------------------------
Bob@bob    | 4       | bob  | 10000  | 123,10,201231

Answer 1

当您从另一个问题 ( SQL most recent order? MS SQL ) 发布查询时，我将使用我的答案，因为它比上述查询更清晰:

SELECT o.*
, OrderID as LastOrderID
FROM (
    SELECT BuyerEMail
    , Name
    , COUNT(*) as TotalOrders
    FROM Orders
    WHERE Pay != 'PayPal'
    GROUP BY BuyerEmail, Name
) o
CROSS APPLY (
    SELECT TOP 1 OrderID, OrderDate
    FROM Orders s
    WHERE s.BuyerEmail = o.BuyerEmail
    ORDER BY OrderDate DESC
) ca

你发布了一个很好的例子，你不完整。您将需要以下 xmlpath 查询:

SELECT OrderID
, Codes
FROM tblLines r1
CROSS APPLY ( 
    SELECT
        STUFF((SELECT ',' + CAST(Code AS NVARCHAR)
        FROM tblLines r2
        WHERE r2.OrderID = r1.OrderID
        GROUP BY OrderID, Code
        ORDER BY Code
        FOR XML PATH (''), TYPE)
        .value('.', 'varchar(max)')
        , 1, 1, ''))  OrderLines(Codes)
GROUP BY OrderID, OrderList

使用简单的连接将其添加到前面的语句中:

SELECT  o.BuyerEMail
    ,o.Name
    ,o.TotalOrders
    , OrderID as LastOrderID
    , c.Codes
FROM (
        SELECT BuyerEMail
        , Name
        , COUNT(*) as TotalOrders
        FROM Orders
        WHERE Pay != 'PayPal'
        GROUP BY BuyerEmail, Name
) o
CROSS APPLY (
        SELECT TOP 1 OrderID, OrderDate
        FROM Orders s
        WHERE s.BuyerEmail = o.BuyerEmail
        ORDER BY OrderDate DESC
) ca 
INNER JOIN (
    SELECT OrderID
    , Codes
    FROM tblLines r1
    CROSS APPLY ( 
        SELECT
            STUFF((SELECT ',' + CAST(Code AS NVARCHAR)
            FROM tblLines r2
            WHERE r2.OrderID = r1.OrderID
            GROUP BY OrderID, Code
            ORDER BY Code
            FOR XML PATH (''), TYPE)
            .value('.', 'varchar(max)')
            , 1, 1, ''))  OrderLines(Codes)
    GROUP BY OrderID, OrderList
) c
ON ca.OrderID = c.OrderID