如何将SQL GROUP BY按年份分组的结果转换为列展示？

针对你的需求，把按id和year分组的结果转换为年份列展示，有两种通用的实现方式，下面结合你的场景具体说明：

方法一：CASE WHEN + 聚合函数（通用所有SQL数据库） #

这种方法兼容性最强，几乎所有关系型数据库都支持。通过CASE WHEN判断年份，结合聚合函数将对应年份的数值汇总，没有数据的年份用0填充：

SELECT
    id,
    COALESCE(SUM(CASE WHEN year = 2018 THEN nb ELSE 0 END), 0) AS nb_2018,
    COALESCE(SUM(CASE WHEN year = 2019 THEN nb ELSE 0 END), 0) AS nb_2019,
    COALESCE(SUM(CASE WHEN year = 2020 THEN nb ELSE 0 END), 0) AS nb_2020
FROM (
    -- 你的原始分组查询
    SELECT 
        id,
        year,
        COUNT(DISTINCT id) AS nb  -- 注：这里按id分组后COUNT(DISTINCT id)恒为1，建议根据实际业务调整为COUNT(*)或其他聚合逻辑
    FROM 
        "data"
    GROUP BY
        id, year
) AS grouped_data
GROUP BY id
ORDER BY id;

方法二：使用数据库内置PIVOT函数（数据库特定） #

部分数据库提供了专门的行转列函数（如SQL Server的PIVOT、PostgreSQL的crosstab），语法更简洁：

SQL Server 示例 #

SELECT
    id,
    ISNULL([2018], 0) AS nb_2018,
    ISNULL([2019], 0) AS nb_2019,
    ISNULL([2020], 0) AS nb_2020
FROM (
    SELECT 
        id,
        year,
        COUNT(DISTINCT id) AS nb
    FROM 
        "data"
    GROUP BY
        id, year
) AS grouped_data
PIVOT (
    SUM(nb)
    FOR year IN ([2018], [2019], [2020])
) AS pivoted_data
ORDER BY id;

PostgreSQL 示例 #

需要先启用tablefunc扩展：

-- 启用扩展（仅首次执行）
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT
    id,
    COALESCE(nb_2018, 0) AS nb_2018,
    COALESCE(nb_2019, 0) AS nb_2019,
    COALESCE(nb_2020, 0) AS nb_2020
FROM crosstab(
    'SELECT id, year, nb FROM (
        SELECT 
            id,
            year,
            COUNT(DISTINCT id) AS nb
        FROM 
            "data"
        GROUP BY
            id, year
    ) AS grouped_data ORDER BY 1,2',
    'VALUES (2018), (2019), (2020)'
) AS ct(id INT, nb_2018 INT, nb_2019 INT, nb_2020 INT);

动态年份的处理 #

如果年份是动态变化的（比如未来会新增2021、2022等），静态写法会维护不便，这时候可以用动态SQL生成列逻辑，以MySQL为例：

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
            'COALESCE(SUM(CASE WHEN year = ', year, ' THEN nb ELSE 0 END), 0) AS nb_', year
        )
    ) INTO @sql
FROM (SELECT DISTINCT year FROM "data") AS years;

SET @sql = CONCAT('SELECT id, ', @sql, ' FROM (
    SELECT 
        id,
        year,
        COUNT(DISTINCT id) AS nb
    FROM 
        "data"
    GROUP BY
        id, year
) AS grouped_data GROUP BY id ORDER BY id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

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