SQL分组查询：GROUP BY后如何拼接行ID并输出计数与ID列表？

我需要编写一条SQL查询，在按title和period进行GROUP BY分组统计数量的同时，输出每组对应的ID列表。目前已有的查询语句如下：

SELECT
    title,
    period,
    COUNT(*)
FROM
    table
GROUP BY
    title, period

示例数据库表结构及数据 #

当前查询输出结果 #

期望输出结果 #

新增一列显示每组的ID列表：

我尝试过使用SELF JOIN和SELECT JOIN，但始终无法写出正确的语法，请问需要如何修改现有查询才能得到期望的输出？

解决方案 #

不同数据库实现字符串聚合的函数不同，以下是主流数据库的写法：

1. MySQL / MariaDB #

使用GROUP_CONCAT()函数，默认用逗号分隔，也可指定分隔符：

SELECT
    title,
    period,
    COUNT(*) AS count,
    GROUP_CONCAT(ID SEPARATOR ', ') AS `Who?`
FROM
    `table`
GROUP BY
    title, period;

2. PostgreSQL #

使用STRING_AGG()函数，数值类型ID需转为文本：

SELECT
    title,
    period,
    COUNT(*) AS count,
    STRING_AGG(ID::TEXT, ', ') AS "Who?"
FROM
    "table"
GROUP BY
    title, period;

3. SQL Server #

SQL Server 2017及以上支持STRING_AGG()：

SELECT
    title,
    period,
    COUNT(*) AS count,
    STRING_AGG(ID, ', ') AS [Who?]
FROM
    [table]
GROUP BY
    title, period;

2016及更早版本需用STUFF+FOR XML PATH组合：

SELECT
    t.title,
    t.period,
    COUNT(*) AS count,
    STUFF(
        (SELECT ', ' + CAST(ID AS VARCHAR)
         FROM [table]
         WHERE title = t.title AND period = t.period
         FOR XML PATH('')),
        1, 2, ''
    ) AS [Who?]
FROM
    [table] t
GROUP BY
    t.title, t.period;

4. Oracle #

使用LISTAGG()函数：

SELECT
    title,
    period,
    COUNT(*) AS count,
    LISTAGG(ID, ', ') WITHIN GROUP (ORDER BY ID) AS "Who?"
FROM
    "table"
GROUP BY
    title, period;

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