MSSQL 2008按id聚合CC_qty总和与Location字段的实现方案

针对你的需求——从三张表提取数据后按id分组，计算CC_qty总和，同时将同一id下的Location合并为单个逗号分隔字段，由于MSSQL 2008不支持LISTAGG或GROUP_CONCAT这类便捷函数，我们可以用FOR XML PATH的经典方案直接通过查询实现，以下是完整的SQL语句：

SELECT
    t.id,
    MAX(t.qty) AS qty, -- 同一id的qty值一致，用MAX/AVG均可保留
    SUM(t.CC_qty) AS CC_qty,
    -- 合并Location字段，去掉开头多余的逗号
    STUFF((
        SELECT ', ' + Location
        FROM (
            -- 你的原始JOIN查询作为子查询
            SELECT 
                a.id, 
                a.qty, 
                b.locationID, 
                b.CC_qty, 
                c.Location 
            FROM ( 
                SELECT LEFT(id, 10) AS id, MAX(qty) AS qty 
                FROM db1 
                WHERE id LIKE 'abc-abc%' 
                GROUP BY LEFT(id, 10) 
            ) AS a 
            JOIN ( 
                SELECT locationID, LEFT(SKU, 10) AS SKU, CC_qty 
                FROM db2 
                WHERE CC_qty > 25 
            ) AS b ON a.id = b.SKU 
            JOIN ( 
                SELECT locationID, Location FROM db3 
            ) AS c ON b.locationID = c.locationID
        ) AS sub
        WHERE sub.id = t.id
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS Location
FROM (
    -- 重复原始JOIN查询作为主查询的数据源
    SELECT 
        a.id, 
        a.qty, 
        b.locationID, 
        b.CC_qty, 
        c.Location 
    FROM ( 
        SELECT LEFT(id, 10) AS id, MAX(qty) AS qty 
        FROM db1 
        WHERE id LIKE 'abc-abc%' 
        GROUP BY LEFT(id, 10) 
    ) AS a 
    JOIN ( 
        SELECT locationID, LEFT(SKU, 10) AS SKU, CC_qty 
        FROM db2 
        WHERE CC_qty > 25 
    ) AS b ON a.id = b.SKU 
    JOIN ( 
        SELECT locationID, Location FROM db3 
    ) AS c ON b.locationID = c.locationID
) AS t
GROUP BY t.id

关键逻辑说明： #

• FOR XML PATH('')：将同一id下的所有Location值拼接成带前置逗号的字符串（例如, NOP11, NOP01）
• STUFF函数：移除字符串开头的, （从第1位开始删除2个字符，替换为空）
• SUM(t.CC_qty)：按id分组对CC_qty求和
• MAX(t.qty)：由于原始查询中同一id的qty值是一致的（来自db1的分组聚合），用MAX或AVG都能保留该值

执行上述查询后，你将得到符合预期的结果（保留所有字段的完整输出如下）：

Array ( 
    [0] => Array ( 
        [id] => abc-abc-12 
        [qty] => 0 
        [CC_qty] => 1635 
        [Location] => NOP11, NOP01 
    ) 
    [1] => Array ( 
        [id] => abc-abc-23 
        [qty] => 0 
        [CC_qty] => 649 
        [Location] => NOP06, NOP08, NOP04 
    ) 
    [2] => Array ( 
        [id] => abc-abc-34 
        [qty] => 0 
        [CC_qty] => 495 
        [Location] => NOP08 
    ) 
    [3] => Array ( 
        [id] => abc-abc-45 
        [qty] => 0 
        [CC_qty] => 461 
        [Location] => NOP05, NOP07 
    ) 
)

内容的提问来源于stack exchange，提问作者mezzomix