如何为分组聚合后的MAX值设置不同列标签？SQL实现疑问

错误点分析 #

• JOIN语法不规范：FULL OUTER JOIN的正确格式为 FULL OUTER JOIN 表/子查询 ON 连接条件，你将两个SELECT语句直接拼接的写法不符合SQL语法规则，且未给子查询指定别名，无法区分不同来源的数据集。
• 列引用歧义：两个子查询都使用t作为表名，连接时id、count列会出现引用歧义，数据库无法识别你指向的是哪个子查询的列。
• 聚合列未命名：CTE中max(count)没有指定别名，后续无法通过t.count正确引用该最大值（会和原表的count列混淆）。

正确实现方案 #

方案1：条件聚合（最简洁高效的行转列方式） #

无需CTE，直接通过CASE WHEN结合聚合函数实现转置：

SELECT 
    id,
    MAX(CASE WHEN category = 'b' THEN count END) AS max_b_count,
    MAX(CASE WHEN category = 'a' THEN count END) AS max_a_count
FROM People
GROUP BY id;

原理：按id分组后，用CASE WHEN筛选对应category的count值，MAX函数会自动保留每个id对应category的最大值（无对应category时返回NULL）。

方案2：修正你的连接思路 #

如果坚持使用CTE+JOIN的方式，需调整语法并明确表别名：

WITH t AS (
    SELECT id, category, MAX(count) AS max_count  -- 给聚合结果添加别名
    FROM People 
    GROUP BY category, id
)
SELECT 
    COALESCE(a.id, b.id) AS id,  -- 处理其中一方id为空的情况
    b.max_count AS max_b_count,
    a.max_count AS max_a_count
FROM (SELECT id, max_count FROM t WHERE category = 'a') a
FULL OUTER JOIN (SELECT id, max_count FROM t WHERE category = 'b') b
ON a.id = b.id;

原理：先通过CTE获取每个id+category的最大值，再分别将category为a、b的数据集作为独立子查询，通过FULL OUTER JOIN按id连接，确保所有id都被保留。

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