sql-server - SQL Server 12：在 OVER PARTITION BY 中生成逗号分隔的字段

Question

我正在构建一个 SQL 查询来对数据进行分组和聚合。结果应该有两个十进制字段和逗号分隔的 varchar 字段的总和：

┌────┬───────────────┬─────────────┬──────────┬────────┬─────┬─────────────┐
│ Id │ InvoiceNumber │ InvoiceDate │ SellerId │ Amount │ Tax │ InvoiceType │
├────┼───────────────┼─────────────┼──────────┼────────┼─────┼─────────────┤
│  1 │ a-001         │ 2019-01-11  │        1 │     10 │   2 │ Regular     │
│  2 │ a-002         │ 2019-01-12  │        1 │     10 │   2 │ Regular     │
│  3 │ a-003         │ 2019-01-13  │        1 │     10 │   2 │ Special     │
│  4 │ a-004         │ 2019-01-14  │        2 │     10 │   2 │ Regular     │
│  5 │ a-005         │ 2019-01-15  │        2 │     10 │   2 │ Regular     │
│  6 │ a-006         │ 2019-01-16  │        3 │     10 │   2 │ Special     │
└────┴───────────────┴─────────────┴──────────┴────────┴─────┴─────────────┘

期望的输出：

┌────┬───────────────┬──────────┬───────────┬────────┬─────┬─────────────────┐
│ Id │ InvoiceNumber │ SellerId │ RowNumber │ Amount │ Tax │   InvoiceType   │
├────┼───────────────┼──────────┼───────────┼────────┼─────┼─────────────────┤
│  3 │ a-003         │        1 │         1 │     30 │   6 │ Regular,Special │
│  5 │ a-005         │        2 │         1 │     20 │   4 │ Regular         │
│  6 │ a-006         │        3 │         1 │     10 │   2 │ Special         │
└────┴───────────────┴──────────┴───────────┴────────┴─────┴─────────────────┘

到目前为止，我尝试过：

SELECT [Id],
    [InvoiceNumber],
    [SellerId],
    ROW_NUMBER() OVER (PARTITION BY [SellerId] ORDER BY [InvoiceDate] DESC) AS [RowNumber],
    SUM([Amount]) OVER (PARTITION BY [SellerId]) AS [Amount], 
    SUM([Tax]) OVER (PARTITION BY [SellerId]) AS [Tax],
    STRING_AGG([InvoiceType], ',')
        OVER (PARTITION BY [SellerId] ORDER BY [InvoiceType]) AS [InvoiceTypes]
FROM [Invoices]
WHERE [RowNumber] = 1

但是，这在 SQL Server 2012 中不起作用，因为它不支持该STRING_AGG()功能。另外，我需要DISTINCT值来避免连接字符串中的重复。有没有办法做到这一点？

注意：问题不是连接，问题是在窗口函数中连接。

标签： sql-serversql-server-2012string-concatenationwindow-functions

Answer 1

SELECT DISTINCT Res1.ID
       ,Res1.InvoiceDate
       ,Res1.SellerID
       ,Res1.Amount
       ,Res1.Tax
       ,(SELECT TOP 1 InvoiceNumber FROM [Invoices] WHERE SellerID = Res1.SellerID ORDER BY InvoiceDate DESC) AS InvoiceNumber
       ,(SELECT STUFF((SELECT DISTINCT ','+InvoiceType
         FROM [Invoices]
         WHERE SellerID = Res1.SellerID
         FOR XML PATH('')),1,1,'')
        ) AS InvoiceType
       FROM 
(
SELECT MAX(ID) AS ID
       ,MAX(InvoiceDate) AS InvoiceDate
       ,SellerID
       ,SUM(Amount) AS Amount
       ,SUM(Tax) AS Tax
FROM [Invoices]
GROUP BY [SellerId]
)Res1