如何用SQL将PostgreSQL表中值组合转为存在标记列？

AI 小北

2026-7-19

PostgreSQL 行列转换实现存在标记视图

要将给定的ID-Color映射表转换为以ID为行、颜色为列并标记存在性的视图，你可以用PostgreSQL的条件聚合或FILTER子句实现，以下是几种可行方案：

方案一：CASE WHEN + 聚合函数

兼容性强，适用于多数SQL数据库：

CREATE VIEW color_pivot AS
SELECT
    id,
    MAX(CASE WHEN color = 'Red' THEN 'X' END) AS red,
    MAX(CASE WHEN color = 'Green' THEN 'X' END) AS green,
    MAX(CASE WHEN color = 'Blue' THEN 'X' END) AS blue
FROM color_data  -- 替换为你的实际表名
GROUP BY id
ORDER BY id;

方案二：PostgreSQL 特有 FILTER 子句

语法更简洁直观，是PostgreSQL的专属语法糖：

CREATE VIEW color_pivot AS
SELECT
    id,
    MAX('X') FILTER (WHERE color = 'Red') AS red,
    MAX('X') FILTER (WHERE color = 'Green') AS green,
    MAX('X') FILTER (WHERE color = 'Blue') AS blue
FROM color_data  -- 替换为你的实际表名
GROUP BY id
ORDER BY id;

方案三：EXISTS 子查询

通过子查询明确判断记录存在性：

CREATE VIEW color_pivot AS
SELECT DISTINCT
    main.id,
    CASE WHEN EXISTS (SELECT 1 FROM color_data cd WHERE cd.id = main.id AND cd.color = 'Red') THEN 'X' END AS red,
    CASE WHEN EXISTS (SELECT 1 FROM color_data cd WHERE cd.id = main.id AND cd.color = 'Green') THEN 'X' END AS green,
    CASE WHEN EXISTS (SELECT 1 FROM color_data cd WHERE cd.id = main.id AND cd.color = 'Blue') THEN 'X' END AS blue
FROM color_data main
ORDER BY id;