PostgreSQL多JOIN查询GROUP BY报错42803的原因及解决方案咨询

报错根本原因 #

PostgreSQL 执行GROUP BY聚合查询时，严格遵循SQL标准规则：SELECT子句中出现的非聚合字段，必须全部出现在GROUP BY子句中。
你在查询里写了A.*，代表取A表的所有字段，但是GROUP BY里仅声明了A.name、A.unit、number三个字段，A表剩余的字段（比如报错提到的A.index）既没有在GROUP BY里声明，也没有用sum/count/max这类聚合函数包裹，数据库无法确定分组后这些字段取哪一行的值，因此抛出42803错误。
你之前逐个加字段的方式不可行，本质是因为A.*会匹配A表所有字段，直到你把A表所有非聚合字段都加到GROUP BY里才会停止报错。

可行解决方法 #

方案1：用A表主键做分组依据（最便捷） #

PostgreSQL 支持特殊规则：如果GROUP BY中包含某张表的唯一主键字段，那么该表的其他所有字段都不需要再额外加入GROUP BY。
假设你A表的主键是id（从你的关联条件B.parent = A.id可以推测id大概率是A的主键），同时你SELECT里还用到了B.child，也需要加入分组，修改后语句如下：

select
    A.*,
    B.child,
    -- 取正则匹配的第一个结果，避免返回数组类型
    (REGEXP_MATCHES(A.b_number, '([^.]*--[0-9]*).*'))[1] as number,
    sum(cast(A.amount AS decimal)) as sum_amount,
    count(A.amount) as cnt_amount
into result
from B
join A on B.name = A.name and B.parent = A.id
join C on A.name = C.name and B.child = C.id
-- 加入A表主键、B.child即可覆盖所有非聚合字段的分组要求
group by A.id, B.child, number;

select * from result;

方案2：明确列出需要的A表字段，避免写A.* #

如果你不需要A表的所有字段，建议直接列出你实际需要的字段，再把这些字段全部加入GROUP BY，语句更清晰，兼容性也更好：

select
    -- 只列你需要的A表字段，比如name、unit、b_number等
    A.name,
    A.unit,
    A.b_number,
    B.child,
    (REGEXP_MATCHES(A.b_number, '([^.]*--[0-9]*).*'))[1] as number,
    sum(cast(A.amount AS decimal)) as sum_amount,
    count(A.amount) as cnt_amount
into result
from B
join A on B.name = A.name and B.parent = A.id
join C on A.name = C.name and B.child = C.id
-- 把SELECT里所有非聚合字段都加进来
group by A.name, A.unit, A.b_number, B.child, number;

select * from result;

方案3：对不需要分组的字段用聚合函数包裹 #

如果你不需要严格保留这些字段的原值，只需要取分组内任意一个值，可以给多余的字段套聚合函数，比如MAX(A.index)，不需要修改GROUP BY：

select
    MAX(A.index) as index,
    A.name,
    A.unit,
    B.child,
    (REGEXP_MATCHES(A.b_number, '([^.]*--[0-9]*).*'))[1] as number,
    sum(cast(A.amount AS decimal)) as sum_amount,
    count(A.amount) as cnt_amount
into result
from B
join A on B.name = A.name and B.parent = A.id
join C on A.name = C.name and B.child = C.id
group by A.name, A.unit, B.child, number;

select * from result;

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