must appear in the GROUP BY clause or be used in an aggregate function
先已知存在一张表test
,表字段数据如下
cname | wmname | avg |
---|---|---|
canada | zoro | 2.00 |
spain | luffy | 1.00 |
spain | usopp | 5.00 |
sql
语句SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname;
同一个sql
语句 在mysql
中是可以正常执行的 但是在pgsql
中会报错
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
字段必须在GROUP BY
中出现或者被用于聚合函数,于是我按照错误提示,把 wmname
字段加在GROUP BY
后面,即出现的结果是
cname | wmname |avg
—|— | —-
canada |zoro | 2.00
spain | luffy | 1.00
spain | usopp | 5.00
而我期望得到的结果是
cname | wmname | avg |
---|---|---|
canada | zoro | 2.00 |
spain | usopp | 5.00 |
在子查询中完成聚合,然后关联包含你想显示字段的表(这里是makerar自身)获取字段(这里是wmname),所以sql就变成了下面这个样子
SELECT
t.cname,
m.wmname,
t.max
FROM
(SELECT
cname,
MAX(avg) AS max
FROM makerar
GROUP BY cname) t
LEFT JOIN makerar m ON t.cname = m.cname AND t.max = m.avg;