如何用SQL的WHERE与AS实现建筑模拟数据的行转列查询

需求背景 #

现有一张存储不同年份建筑模拟数据的表simulation_output，每行对应单个年份，列名包括id、year、avg_heat_demand、renovation_level、co2_emission等。需要创建新表output_time，将2015、2025、2050年的avg_heat_demand分别作为独立列，新表结构如下：

id / avg_heat_demand_2015 / avg_heat_demand_2025 / avg_heat_demand_2050

尝试的错误SQL语句 #

你之前尝试的写法存在语法和逻辑问题，具体语句如下：

CREATE TABLE output_time AS 
SELECT id, year, 
       (avg_heat_demand WHERE year = 2015) AS avg_heat_demand_m2_2015, 
       (avg_heat_demand WHERE year = 2050) AS avg_heat_demand_m2_2025, 
       (avg_heat_demand WHERE year = 2050) AS avg_heat_demand_m2_2050 
FROM simulation_output;

示例数据 #

原表simulation_output的示例数据：

id | year | avg_heat_demand | etc 
----+-----+-----------------+---- 
11 | 2015 | 55              | ...
12 | 2015 | 40              | ...
11 | 2016 | 48              | ...
12 | 2016 | 49              | ...
11 | 2025 | 45              | ...
12 | 2025 | 43              | ...
11 | 2050 | 50              | ...
12 | 2050 | 52              | ...

期望结果 #

新表output_time的期望输出：

id | avg_heat_demand_2015 | avg_heat_demand_2025 | avg_heat_demand_2050 
---+----------------------+----------------------+--------------------- 
11 | 55                   | 45                   | 50 
12 | 40                   | 43                   | 52 

正确的SQL解决方案 #

你这里遇到的是典型的**行转列（Pivot）**需求，原来的写法有两个核心问题：一是用WHERE直接嵌套在字段里的语法不合法，二是没有按id分组，会返回大量重复行。下面给你两种可行的方案：

方案一：通用写法（兼容所有关系型数据库） #

这是最稳妥的写法，几乎所有数据库都支持：

CREATE TABLE output_time AS
SELECT 
    id,
    MAX(CASE WHEN year = 2015 THEN avg_heat_demand END) AS avg_heat_demand_2015,
    MAX(CASE WHEN year = 2025 THEN avg_heat_demand END) AS avg_heat_demand_2025,
    MAX(CASE WHEN year = 2050 THEN avg_heat_demand END) AS avg_heat_demand_2050
FROM simulation_output
WHERE year IN (2015, 2025, 2050) -- 提前过滤无关年份，提升查询效率
GROUP BY id;

逻辑说明

• CASE WHEN会在年份匹配时返回对应的avg_heat_demand值，不匹配时返回NULL；
• MAX()聚合函数会提取每个id下的非NULL值（因为每个id对应每个年份只有一条数据，所以MAX拿到的就是唯一有效值）；
• GROUP BY id确保每个id只生成一行结果，完全匹配你想要的输出结构；
• 加上WHERE条件过滤掉不需要的年份，能减少数据库计算量，让查询更快。

方案二：数据库原生PIVOT语法（部分数据库支持） #

如果你的数据库是Oracle、SQL Server或MySQL 8.0+这类支持原生PIVOT操作的，可以用更简洁的写法，以SQL Server为例：

CREATE TABLE output_time AS
SELECT id, [2015] AS avg_heat_demand_2015, [2025] AS avg_heat_demand_2025, [2050] AS avg_heat_demand_2050
FROM (
    SELECT id, year, avg_heat_demand
    FROM simulation_output
    WHERE year IN (2015,2025,2050)
) AS SourceTable
PIVOT (
    MAX(avg_heat_demand)
    FOR year IN ([2015], [2025], [2050])
) AS PivotTable;

注意：不同数据库的PIVOT语法细节有差异，比如PostgreSQL需要用crosstab函数，所以如果要跨数据库兼容，还是第一种CASE WHEN的写法更稳妥。

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