MySQL：如何避免在SELECT语句中手动编写年份列，改用循环或其他结构？

我是SQL新手，若能告知我可深入学习该问题的资料，我将十分感激。我想要生成一张行代表国家、列代表年份的表格，展示对应年份的相关数值数据，当前使用的SQL语句如下：

SELECT
  country,
  SUM(CASE year
      WHEN 2019 THEN number
      ELSE 0) AS '2019',
  SUM(CASE year
      WHEN 2020 THEN number
      ELSE 0) AS '2020',
  SUM(CASE year
      WHEN 2021 THEN number
      ELSE 0) AS '2021',
  SUM(CASE year
      WHEN 2022 THEN number
      ELSE 0) AS '2022'
FROM
  table
GROUP BY
  country

我的问题是：能否避免手动编写所有这些年份列，转而使用循环或其他结构/语句？某些场景下需要生成大量列，手动编写逻辑耗时过长。

可以通过动态SQL自动生成年份列，无需手动编写重复的CASE语句，具体实现取决于你使用的数据库：

主流数据库的动态SQL实现 #

• MySQL/MariaDB：利用预处理语句拼接SQL
  先查询表中所有不重复的年份，自动生成CASE字段部分，再执行完整SQL：

  SET @sql = NULL;
  SELECT GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN year = ', year, ' THEN number ELSE 0 END) AS ''', year, ''''
    )
  ) INTO @sql
  FROM your_table;
  
  SET @sql = CONCAT('SELECT country, ', @sql, ' FROM your_table GROUP BY country');
  
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  

  注：如果年份数量过多，需调整group_concat_max_len参数防止SQL字符串被截断。

• SQL Server：使用STRING_AGG拼接字段（2017+版本支持）

  DECLARE @sql NVARCHAR(MAX);
  SELECT @sql = STRING_AGG(
    CONCAT('SUM(CASE WHEN year = ', year, ' THEN number ELSE 0 END) AS [', year, ']'),
    ', '
  )
  FROM (SELECT DISTINCT year FROM your_table) AS Years;
  
  SET @sql = N'SELECT country, ' + @sql + N' FROM your_table GROUP BY country';
  
  EXEC sp_executesql @sql;
  

• PostgreSQL：通过字符串拼接+EXECUTE执行

  DO $$
  DECLARE
    sql TEXT;
  BEGIN
    SELECT string_agg(
      CONCAT('SUM(CASE WHEN year = ', year, ' THEN number ELSE 0 END) AS "', year, '"'),
      ', '
    ) INTO sql
    FROM (SELECT DISTINCT year FROM your_table) AS Years;
  
    sql := 'SELECT country, ' || sql || ' FROM your_table GROUP BY country';
    EXECUTE sql;
  END $$;
  

额外提示 #

• 如果是做报表展示，优先考虑BI工具（如Tableau、Power BI）的原生透视功能，拖拽字段即可生成表格，比写SQL更高效。
• 动态SQL虽然灵活，但要注意维护成本，若年份是用户输入的场景，需防范SQL注入风险（本文案例中年份来自表数据，风险较低）。

学习资料推荐 #

• 优先查阅你所用数据库的官方文档，搜索「动态SQL」「行列转换」「透视表」相关章节，官方文档是最权威的参考。
• 进阶书籍：《SQL进阶教程》（MICK 著）有专门章节讲解动态SQL和行列转换技巧；《高性能MySQL》包含动态SQL的实践案例，适合深入学习。

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