PostgreSQL按两列分组并将其中一列转为结果展示列的实现咨询

开发者阿泽

2026-9-25

1 | fruit
2 | drink
3 | vege
4 | fish

以及`Journal`表：

Journal
id | subj | reference | value

1 | 1 | foo | 30
2 | 2 | bar | 20
3 | 1 | bar | 35
4 | 1 | bar | 10
5 | 2 | baz | 25
6 | 4 | foo | 30
7 | 4 | bar | 40
8 | 1 | baz | 20
9 | 2 | bar | 5

我需要对`Journal.value`字段求和，同时按`subj`和`reference`两个字段分组。
我知道`group by`子句可实现基础分组，但我期望得到如下格式的输出结果：

reference | subj_1 | subj_2 | subj_3 | subj_4
| fruit | drink | vege | fish (even better)

foo |     30 |        |        |     30
  bar |     45 |     25 |        |     40
  baz |     20 |     25 |        |

请问该需求是否可以实现？
---
### 解答
完全可以实现，这是典型的行转列（透视表）需求，给你两种常用的实现方案：
#### 方案1：全数据库兼容的CASE WHEN聚合写法
不依赖任何数据库专有函数，所有支持标准SQL的数据库都可以使用，思路是先按`reference`分组，每个分组内通过`CASE`语句判断`subj`取值，对匹配的`value`求和即可：
```sql
SELECT 
  reference,
  SUM(CASE WHEN subj = 1 THEN value END) AS 'fruit(subj_1)',
  SUM(CASE WHEN subj = 2 THEN value END) AS 'drink(subj_2)',
  SUM(CASE WHEN subj = 3 THEN value END) AS 'vege(subj_3)',
  SUM(CASE WHEN subj = 4 THEN value END) AS 'fish(subj_4)'
FROM Journal
GROUP BY reference
ORDER BY reference;

执行结果完全匹配你需要的格式，列名可以根据自己的使用习惯调整。

方案2：支持PIVOT函数的数据库简化写法

如果你使用的是SQL Server、Oracle、PostgreSQL 15+、MySQL 8.0.19及以上版本，可以用内置的PIVOT透视函数实现更简洁的写法：

SELECT *
FROM (
  SELECT reference, subj, value FROM Journal
) AS raw_data
PIVOT (
  SUM(value)
  FOR subj IN (
    1 AS 'fruit(subj_1)',
    2 AS 'drink(subj_2)',
    3 AS 'vege(subj_3)',
    4 AS 'fish(subj_4)'
  )
) AS pivot_result
ORDER BY reference;