如何在SELECT语句中按固定行数（如3行）合并Concat表中数据？

问题背景 #

现有数据表 readings，包含两列：

• id_readings：自增主键
• name：字符串类型

数据表原始数据如下：

需求：不考虑id_readings或name的取值，将数据按行顺序每3行的name字段合并为一行，用逗号分隔。

期望输出结果：

解决方案 #

核心思路是先给每行数据分配分组编号（每3行一组），再对每组的name进行字符串聚合。以下是不同数据库的实现代码：

1. MySQL/MariaDB #

SELECT GROUP_CONCAT(name SEPARATOR ',') AS concat
FROM (
    SELECT name,
           FLOOR((ROW_NUMBER() OVER (ORDER BY id_readings) - 1) / 3) AS group_id
    FROM readings
) AS grouped
GROUP BY group_id;

2. PostgreSQL #

SELECT STRING_AGG(name, ',' ORDER BY id_readings) AS concat
FROM (
    SELECT name,
           FLOOR((ROW_NUMBER() OVER (ORDER BY id_readings) - 1) / 3) AS group_id
    FROM readings
) AS grouped
GROUP BY group_id;

3. SQL Server #

支持STRING_AGG的版本（2017+）

SELECT STRING_AGG(name, ',') WITHIN GROUP (ORDER BY id_readings) AS concat
FROM (
    SELECT name,
           FLOOR((ROW_NUMBER() OVER (ORDER BY id_readings) - 1) / 3) AS group_id
    FROM readings
) AS grouped
GROUP BY group_id;

2016及更早版本

SELECT STUFF(
           (SELECT ',' + name
            FROM readings r2
            WHERE FLOOR((ROW_NUMBER() OVER (ORDER BY r2.id_readings) - 1) / 3) = r1.group_id
            ORDER BY r2.id_readings
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
           1, 1, ''
       ) AS concat
FROM (
    SELECT name,
           FLOOR((ROW_NUMBER() OVER (ORDER BY id_readings) - 1) / 3) AS group_id
    FROM readings
) AS r1
GROUP BY r1.group_id;

4. Oracle #

SELECT LISTAGG(name, ',') WITHIN GROUP (ORDER BY id_readings) AS concat
FROM (
    SELECT name,
           FLOOR((ROW_NUMBER() OVER (ORDER BY id_readings) - 1) / 3) AS group_id
    FROM readings
)
GROUP BY group_id;

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