如何按ID合并同ID多行COMMENT并按SEQUENCE排序以'/ '分隔？

要实现将相同ID对应的COMMENT按SEQUENCE顺序拼接，核心是利用各数据库自带的字符串聚合函数，结合分组和排序来完成。以下是主流数据库的具体实现代码：

MySQL/MariaDB #

使用GROUP_CONCAT函数，通过ORDER BY指定SEQUENCE排序，同时设置分隔符为/ ：

SELECT
    F.ID,
    F.FRUIT,
    GROUP_CONCAT(C.COMMENT ORDER BY C.SEQUENCE SEPARATOR '/ ') AS COMMENT
FROM FRUIT F
LEFT JOIN COMMENT C 
ON F.ID = C.ID
GROUP BY F.ID, F.FRUIT;

PostgreSQL #

使用STRING_AGG函数，直接在函数内指定排序规则：

SELECT
    F.ID,
    F.FRUIT,
    STRING_AGG(C.COMMENT, '/ ' ORDER BY C.SEQUENCE) AS COMMENT
FROM FRUIT F
LEFT JOIN COMMENT C 
ON F.ID = C.ID
GROUP BY F.ID, F.FRUIT;

SQL Server #

• 2017及以上版本：使用STRING_AGG配合WITHIN GROUP指定排序

SELECT
    F.ID,
    F.FRUIT,
    STRING_AGG(C.COMMENT, '/ ') WITHIN GROUP (ORDER BY C.SEQUENCE) AS COMMENT
FROM FRUIT F
LEFT JOIN COMMENT C 
ON F.ID = C.ID
GROUP BY F.ID, F.FRUIT;

• 旧版本（2016及以下）：用STUFF+FOR XML PATH实现拼接

SELECT
    F.ID,
    F.FRUIT,
    STUFF(
        (SELECT '/ ' + C2.COMMENT
         FROM COMMENT C2
         WHERE C2.ID = F.ID
         ORDER BY C2.SEQUENCE
         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
        1, 2, ''
    ) AS COMMENT
FROM FRUIT F
GROUP BY F.ID, F.FRUIT;

Oracle #

使用LISTAGG函数完成聚合排序：

SELECT
    F.ID,
    F.FRUIT,
    LISTAGG(C.COMMENT, '/ ') WITHIN GROUP (ORDER BY C.SEQUENCE) AS COMMENT
FROM FRUIT F
LEFT JOIN COMMENT C 
ON F.ID = C.ID
GROUP BY F.ID, F.FRUIT;

你之前尝试GROUP BY未成功，大概率是没用到对应数据库的字符串聚合函数，或者没有在聚合过程中指定按SEQUENCE排序，导致拼接顺序不符合预期。

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