sql - 必须出现在 GROUP BY 子句中或在聚合函数中使用

Question

我有一个看起来像调用者“makerar”的表

<表类=“s-表”><标题>cnamewmname平均 <正文>加拿大佐罗2.0000000000000000西类牙路飞1.00000000000000000000西类牙乌索普5.0000000000000000

我想为每个 cname 选择最大平均值。

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;

所以我这样做

SELECT cname, wmname, MAX(avg)  FROM makerar GROUP BY cname, wmname;

但是这不会给出预期的结果，并且显示下面的错误输出。

<表类=“s-表”><标题>cnamewmname最大 <正文>加拿大佐罗2.0000000000000000西类牙路飞1.00000000000000000000西类牙乌索普5.0000000000000000

实际结果应该是

<表类=“s-表”><标题>cnamewmname最大 <正文>加拿大佐罗2.0000000000000000西类牙乌索普5.0000000000000000

我该如何解决这个问题？

注意:该表是根据先前操作创建的 View 。

Answer 1

是的，这是一个常见的聚合问题。之前SQL3 (1999) ，所选字段必须出现在 GROUP BY 子句[*]中。

要解决此问题，您必须在子查询中计算聚合，然后将其与其自身连接以获取需要显示的其他列:

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

<小时/>

但是你也可以使用窗口函数，这看起来更简单:

SELECT cname, wmname, MAX(avg) OVER (PARTITION BY cname) AS mx
FROM makerar
;

此方法的唯一问题是它将显示所有记录(窗口函数不分组)。但它会在每行中显示国家/地区的正确值(即在 cname 级别最大)MAX，因此这取决于您:

 cname  | wmname |          mx           
--------+--------+------------------------
 canada | zoro   |     2.0000000000000000
 spain  | luffy  |     5.0000000000000000
 spain  | usopp  |     5.0000000000000000

显示唯一与最大值匹配的 (cname, wmname) 元组的解决方案可以说不太优雅，是:

SELECT DISTINCT /* distinct here matters, because maybe there are various tuples for the same max value */
    m.cname, m.wmname, t.avg AS mx
FROM (
    SELECT cname, wmname, avg, ROW_NUMBER() OVER (PARTITION BY avg DESC) AS rn 
    FROM makerar
) t JOIN makerar m ON m.cname = t.cname AND m.wmname = t.wmname AND t.rn = 1
;


 cname  | wmname |          mx           
--------+--------+------------------------
 canada | zoro   |     2.0000000000000000
 spain  | usopp  |     5.0000000000000000

<小时/>

[*]:有趣的是，尽管规范允许选择非分组字段，但主要引擎似乎并不真正喜欢它。 Oracle 和 SQLServer 根本不允许这样做。 Mysql 过去默认允许它，但现在从 5.7 开始，管理员需要在服务器配置中手动启用此选项(ONLY_FULL_GROUP_BY)才能支持此功能...