SQL实现多行合并为单行（任务编号、供应商两个字段分别合并）

需求实现方案 #

以下是不同数据库环境下的正确实现逻辑，你之前用STUFF未得到预期结果大概率是未对两个拼接字段分别写独立子查询导致的错位重复问题：

SQL Server 实现（STUFF 函数正确写法） #

SELECT
    req.number AS req_number,
    -- 拼接所有关联的task.number
    STUFF((
        SELECT ',' + task.number 
        FROM task 
        WHERE req.sys_id = task.req_item
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS task_numbers,
    -- 拼接所有关联的usr.vendor
    STUFF((
        SELECT ',' + ISNULL(usr.vendor, '') 
        FROM task
        LEFT JOIN usr ON usr.sys_id = task.assigned_to
        WHERE req.sys_id = task.req_item
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS vendor_list
FROM req
GROUP BY req.number, req.sys_id

可选优化：如果需要去重，可以在子查询的SELECT后加DISTINCT关键字。

MySQL 实现 #

SELECT
    req.number AS req_number,
    GROUP_CONCAT(DISTINCT task.number SEPARATOR ',') AS task_numbers,
    GROUP_CONCAT(DISTINCT IFNULL(usr.vendor, '') SEPARATOR ',') AS vendor_list
FROM req
LEFT JOIN task on req.sys_id = task.req_item
LEFT JOIN usr on usr.sys_id = task.assigned_to
GROUP BY req.number, req.sys_id

PostgreSQL 实现 #

SELECT
    req.number AS req_number,
    STRING_AGG(DISTINCT task.number, ',') AS task_numbers,
    STRING_AGG(DISTINCT COALESCE(usr.vendor, ''), ',') AS vendor_list
FROM req
LEFT JOIN task on req.sys_id = task.req_item
LEFT JOIN usr on usr.sys_id = task.assigned_to
GROUP BY req.number, req.sys_id

Oracle 实现 #

SELECT
    req.number AS req_number,
    LISTAGG(DISTINCT task.number, ',') WITHIN GROUP (ORDER BY task.number) AS task_numbers,
    LISTAGG(DISTINCT NVL(usr.vendor, ''), ',') WITHIN GROUP (ORDER BY usr.vendor) AS vendor_list
FROM req
LEFT JOIN task on req.sys_id = task.req_item
LEFT JOIN usr on usr.sys_id = task.assigned_to
GROUP BY req.number, req.sys_id

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