Inner Join分组查询报错：列b.db需在GROUP BY或聚合函数中使用

这个报错的核心逻辑是SQL标准（以及PostgreSQL等多数数据库的严格模式）要求：SELECT子句中的非聚合字段，必须全部出现在GROUP BY子句中。你的查询里，SELECT了b.db、b.schemaname等来自表b的字段，但这些字段既没加入GROUP BY，也没像表a的字段那样用MAX()这类聚合函数包裹——数据库无法确定要返回表b的哪一条对应记录，因此触发报错。

针对不同业务场景的解决方案 #

场景1：每组(a.schemaname, a.tablename)关联的表b记录唯一

如果(b.schemaname, b.tablename)是表b的唯一键（即每个表名+ schema名组合在b中只对应一条记录），推荐以下两种高效写法：

写法1：将表b的所有非聚合字段加入GROUP BY

select a.schemaname,a.tablename,max(a.tbl_cmmt_txt) as tbl_cmmt_txt,
max(a.cleansed_tbl_cmmt_txt) as cleansed_tbl_cmmt_txt,max(a.tbl_dscr_txt) as tbl_dscr_txt,
max(a.cleansed_tbl_dscr_txt)as cleansed_tbl_dscr_txt,max(a.tbl_annt_txt) as tbl_annt_txt,
max(a.cleansed_tbl_annt_txt) as cleansed_tbl_annt_txt,max(a.dw_etl_sess_nm),max(a.dw_ld_grp_val),
max(a.dw_ins_dts),max(a.dw_upd_dts),
b.db,b.schemaname,b.tablename,b.tabledescription,b.table_id,b.ts_updated,b.user_name
from test.sample a INNER JOIN  test.sample1 b
ON lower(a.schemaname)=lower(b.schemaname) AND lower(a.tablename)=lower(b.tablename)
where a.schemaname is not null
group by a.schemaname,a.tablename,b.db,b.schemaname,b.tablename,b.tabledescription,b.table_id,b.ts_updated,b.user_name

写法2：先聚合表a，再关联表b（更高效，先缩小数据集）

select agg_a.schemaname,agg_a.tablename,agg_a.tbl_cmmt_txt,agg_a.cleansed_tbl_cmmt_txt,
agg_a.tbl_dscr_txt,agg_a.cleansed_tbl_dscr_txt,agg_a.tbl_annt_txt,agg_a.cleansed_tbl_annt_txt,
agg_a.dw_etl_sess_nm,agg_a.dw_ld_grp_val,agg_a.dw_ins_dts,agg_a.dw_upd_dts,
b.db,b.schemaname,b.tablename,b.tabledescription,b.table_id,b.ts_updated,b.user_name
from (
    select schemaname,tablename,
    max(tbl_cmmt_txt) as tbl_cmmt_txt,
    max(cleansed_tbl_cmmt_txt) as cleansed_tbl_cmmt_txt,
    max(tbl_dscr_txt) as tbl_dscr_txt,
    max(cleansed_tbl_dscr_txt) as cleansed_tbl_dscr_txt,
    max(tbl_annt_txt) as tbl_annt_txt,
    max(cleansed_tbl_annt_txt) as cleansed_tbl_annt_txt,
    max(dw_etl_sess_nm) as dw_etl_sess_nm,
    max(dw_ld_grp_val) as dw_ld_grp_val,
    max(dw_ins_dts) as dw_ins_dts,
    max(dw_upd_dts) as dw_upd_dts
    from test.sample
    where schemaname is not null
    group by schemaname,tablename
) agg_a
INNER JOIN test.sample1 b
ON lower(agg_a.schemaname)=lower(b.schemaname) AND lower(agg_a.tablename)=lower(b.tablename)

场景2：每组(a.schemaname, a.tablename)关联多条表b记录

如果一个表名+ schema名组合在b中有多条记录，需要根据业务需求给b的字段添加聚合函数，比如用MAX()取最新值、STRING_AGG()合并内容等：

select a.schemaname,a.tablename,max(a.tbl_cmmt_txt) as tbl_cmmt_txt,
max(a.cleansed_tbl_cmmt_txt) as cleansed_tbl_cmmt_txt,max(a.tbl_dscr_txt) as tbl_dscr_txt,
max(a.cleansed_tbl_dscr_txt)as cleansed_tbl_dscr_txt,max(a.tbl_annt_txt) as tbl_annt_txt,
max(a.cleansed_tbl_annt_txt) as cleansed_tbl_annt_txt,max(a.dw_etl_sess_nm),max(a.dw_ld_grp_val),
max(a.dw_ins_dts),max(a.dw_upd_dts),
max(b.db) as db,max(b.schemaname) as b_schemaname,max(b.tablename) as b_tablename,
max(b.tabledescription) as tabledescription,max(b.table_id) as table_id,
max(b.ts_updated) as ts_updated,max(b.user_name) as user_name
from test.sample a INNER JOIN  test.sample1 b
ON lower(a.schemaname)=lower(b.schemaname) AND lower(a.tablename)=lower(b.tablename)
where a.schemaname is not null
group by a.schemaname,a.tablename

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