PostgreSQL使用CTE配合聚合函数查询报GROUP BY错误如何解决

• 分组规则违反SQL标准：PostgreSQL对分组查询的校验严格遵循SQL规范，使用GROUP BY聚合时，SELECT子句中未被聚合函数包裹的字段，必须全部纳入GROUP BY的字段列表。你的主查询仅按table_2.id_table_2分组，但CASE表达式中直接引用了CTE返回的subq.max_date_table_1、subq.count_table_1两个字段，这两个字段既没有被聚合函数处理，也没有加入GROUP BY列表，因此触发你看到的报错。
• CTE内部存在字段引用错误：CTE的WHERE子句中引用了SELECT阶段才定义的别名max_date_table_1，SQL执行顺序中WHERE子句的计算早于SELECT子句，WHERE阶段无法识别这个别名，运行时会报字段不存在错误；且你的筛选逻辑是取近30天的table_1记录，直接判断原始date_字段即可，不需要引用聚合后的别名。
• 表连接语法错误：主查询使用逗号隐式连接两个表时，错误地直接在表名后加AND拼接连接条件，会触发语法报错，应该使用标准的JOIN...ON写法关联两表。

由于CTEsubq本身已经按id_table_1做了聚合，每个id_table_1只会对应唯一的max_date_table_1和count_table_1值，和table_2按id关联后属于1:1匹配关系，只需要把CTE的两个字段加入GROUP BY列表，同时修正CTE筛选逻辑和表连接语法即可，修正后的完整代码如下：

WITH
subq AS (
    SELECT 
        id_table_1, 
        COUNT(*) AS count_table_1, 
        DATE(MAX(date_)) AS max_date_table_1
    FROM table_1
    WHERE DATE(date_) > DATE(NOW()) - INTERVAL '30 DAYS'
    GROUP BY id_table_1
)

SELECT 
    table_2.id_table_2,
    CASE
        WHEN MAX(table_2.date_) > subq.max_date_table_1 THEN 0
        ELSE subq.count_table_1
    END AS RESULT
FROM table_2
INNER JOIN subq
    ON table_2.id_table_2 = subq.id_table_1
GROUP BY table_2.id_table_2, subq.max_date_table_1, subq.count_table_1;

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