SQL Server：去除Line_BusinessUnit重复词及分组求和查询优化

希望通过以下简化查询获取数据：

SELECT 
     [ReceiptSeq]
    ,[NetAmount]
    ,[Line_BusinessUnit]
FROM [TB_TEST]
ORDER BY [ReceiptSeq]

查询结果：

需求：按[ReceiptSeq]分组，汇总NetAmount，同时选取Line_BusinessUnit的唯一值，期望结果如下：

测试表创建及数据插入脚本 #

CREATE TABLE [dbo].[TB_TEST](
    [ReceiptSeq] [nvarchar](20) NULL,
    [NetAmount] [decimal](10, 2) NULL,
    [Line_BusinessUnit] [nvarchar](1000) NULL
) ON [PRIMARY]

INSERT INTO [dbo].[TB_TEST] VALUES(1,33.00,'Powder')
INSERT INTO [dbo].[TB_TEST] VALUES(1,33.00,'Powder')
INSERT INTO [dbo].[TB_TEST] VALUES(2,11.00,'Powder')
INSERT INTO [dbo].[TB_TEST] VALUES(3,252.00,'Powder')
INSERT INTO [dbo].[TB_TEST] VALUES(3,36.00,'Powder')

解决方案SQL #

由于同一ReceiptSeq分组内的Line_BusinessUnit值唯一，直接分组后用SUM汇总金额，用MAX或MIN提取唯一值即可：

SELECT 
     [ReceiptSeq]
    ,SUM([NetAmount]) AS NetAmount
    ,MAX([Line_BusinessUnit]) AS Line_BusinessUnit
FROM [TB_TEST]
GROUP BY [ReceiptSeq]
ORDER BY [ReceiptSeq]

当前SQL Server查询返回的Line_BusinessUnit列存在重复词，原查询语句如下：

SELECT 
    i.[PO_Number] AS PO_Number, 
    i.[ReceiptSeq] AS Receipt#, 
    SUM(i.[Tax]) AS [Tax],
    SUM(i.[NetAmount]) AS [NetAmount],
    SUM(i.[ReceivedTotal]) AS [ReceivedTotal],
    i.[InvoiceNbr],
    i.[InvoiceDate],
    i.[Line_GLAccount],
    i.[Line_GLSubCode],
    STRING_AGG(i.[Line_BusinessUnit], ', ') AS Line_BusinessUnit 
FROM  
    dbo.[TB_TEST] i
GROUP BY
    i.[PO_Number], i.[ReceiptSeq], 
    i.[InvoiceNbr], i.[InvoiceDate],
    i.[Line_GLAccount], i.[Line_GLSubCode]

问题根源：STRING_AGG直接拼接分组内所有Line_BusinessUnit值，部分行的字段本身包含重复词（如Powder, Powder），跨行拼接也会加剧重复。需要先拆分字符串去重，再重新聚合。

测试表创建及数据插入脚本 #

CREATE TABLE [dbo].[TB_TEST]
(
    [PO_Number] [nvarchar](20) NULL,
    [ReceiptSeq] [nvarchar](20) NULL,
    [Tax] [decimal](10, 2) NULL,
    [NetAmount] [decimal](10, 2) NULL,
    [ReceivedTotal] [decimal](10, 2) NULL,
    [InvoiceNbr] [nvarchar](50) NULL,
    [InvoiceDate] [datetime] NULL,
    [Line_GLAccount] [nvarchar](50) NULL,
    [Line_GLSubCode] [nvarchar](50) NULL,
    [Line_BusinessUnit] [nvarchar](1000) NULL
) ON [PRIMARY]

INSERT INTO [TB_TEST] VALUES('143231',' 2',1.65,11.00,12.65,'000580163,000580262',NULL,'3031030503','510105','Powder')
INSERT INTO [TB_TEST] VALUES('143231',' 3',206.40,1376.00,1582.40,'000580163,000580262',NULL,'3031030503','510105','Powder, Powder, Powder, Powder, Powder')
INSERT INTO [TB_TEST] VALUES('143231',' 4',23.33,155.50,178.83,'25000342960','2025-02-01 00:00:00.000','3031030609','51011299','Powder, UHT, Both')

优化后的查询语句 #

使用STRING_SPLIT拆分字符串，结合DISTINCT去重后再用STRING_AGG聚合：

SELECT 
    t.[PO_Number], 
    t.[ReceiptSeq] AS Receipt#, 
    SUM(t.[Tax]) AS [Tax],
    SUM(t.[NetAmount]) AS [NetAmount],
    SUM(t.[ReceivedTotal]) AS [ReceivedTotal],
    t.[InvoiceNbr],
    t.[InvoiceDate],
    t.[Line_GLAccount],
    t.[Line_GLSubCode],
    STRING_AGG(DISTINCT TRIM(s.value), ', ') AS Line_BusinessUnit
FROM  
    dbo.[TB_TEST] t
CROSS APPLY STRING_SPLIT(t.[Line_BusinessUnit], ',') s
GROUP BY
    t.[PO_Number], t.[ReceiptSeq], 
    t.[InvoiceNbr], t.[InvoiceDate],
    t.[Line_GLAccount], t.[Line_GLSubCode]

说明：

• CROSS APPLY STRING_SPLIT将每行的Line_BusinessUnit拆分为单个单元
• TRIM去除拆分后每个单元的前后空格，避免因空格导致的重复识别失败
• DISTINCT在STRING_AGG中过滤重复的业务单元值
• 聚合逻辑和分组字段保持原查询的业务规则不变

内容的提问来源于stack exchange，提问作者Peter Nguy Nguyen