解决报错:must appear in the GROUP BY clause or be used in an aggregate function
今天使用Postgres数据库在编写sql 时,执行后发现报错:column "XXXXXX" must appear in the GROUP BY clause or be used in an aggregate function 根据提示把查询的字段放到分组后,查询的结果又不是自己需要的,在网上查询了一下解决方法,特此记录
问题产生的原因:这是pgsql一个常见的聚合问题，在SQL3标准以前，选择显示的字段必须出现在在 GROUP BY 中
假设存在makerar表,数据如下:
cname | wmname | avg --------+-------------+------------------------ canada | zoro | 2.0000000000000000 spain | luffy | 1.00000000000000000000 spain | usopp | 5.0000000000000000
我想以cname分组查询到avg值最大的数据,sql如下
SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname;
但是,报错
ERROR: column "makerar.wmname" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname;
所以,我把wmname加入的分组后面
SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname, wmname;
结果如下,但这不是我想要的数据
cname | wmname | max --------+--------+------------------------ canada | zoro | 2.0000000000000000 spain | luffy | 1.00000000000000000000 spain | usopp | 5.0000000000000000
我想要的数据结果为
cname | wmname | max --------+--------+------------------------ canada | zoro | 2.0000000000000000 spain | usopp | 5.0000000000000000
整理出两种方式解决该问题:
一. 使用嵌套sql,在子查询中计算聚合，然后将其与自身连接，以获得需要显示的附加列
SELECT m.cname, m.wmname, t.mx FROM ( SELECT cname, MAX(avg) AS mx FROM makerar GROUP BY cname ) t JOIN makerar m ON m.cname = t.cname AND t.mx = m.avg ; cname | wmname | mx --------+--------+------------------------ canada | zoro | 2.0000000000000000 spain | usopp | 5.0000000000000000
二.使用特殊的DISTINCT ON表达式
SELECT DISTINCT ON (cname) cname, wmname, avg FROM makerar ORDER BY cname, avg DESC ;
结合自身业务,使用第一种方式改造sql
SELECT pihs.pat_status, b.bed_class_id, pih.charge_class_id, pihs.pat_condition, pih.pat_id, pihs.room_with_mother_baby_flag, pc.pat_in_charge_doc_name, pc.pat_in_charge_doc_id, b.bed_show_no, pih.pat_age AS age, pih.pat_in_hos_id, pih.pat_in_hos_code, pihs.pat_in_status, pc.state_of_critical_value, pih.pat_in_time, pc.chief_doc_id, pc.chief_doc_name, pc.dur_nurse_id, pc.dur_nurse_name, pc.first_ann_bed_time, pc.manage_bed_nurse_name, pih.pat_type_id, pc.pat_clinic_id, pc.pat_ward_id, b.bed_id, b.bed_status, b.is_share, pc.nursing_class, pc.nursing_class_name, pihs.pat_in_bed_share_status, pih.seven_days_again_in_flag, b.price, T.change_in_time FROM bed b LEFT JOIN pat_in_hospital_status pihs ON b.bed_id = pihs.bed_id LEFT JOIN pat_in_hospital pih ON b.pat_in_hos_id = pih.pat_in_hos_id LEFT JOIN pat_clinican pc ON pih.pat_in_hos_id = pc.pat_in_hos_id LEFT JOIN ( SELECT pat_in_hos_id, MAX ( pat_in_out_ward_time ) AS change_in_time FROM change_ward_record WHERE complete_flag = '1' GROUP BY pat_in_hos_id ) T ON pih.pat_in_hos_id = T.pat_in_hos_id where b.ward_id='1234'
参考链接:
