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

Question

我有一张桌子看起来像这个叫“Makerar”的人

 cname  | wmname |          avg           
--------+-------------+------------------------
 canada | zoro   |     2.0000000000000000
 spain  | luffy  | 1.00000000000000000000
 spain  | usopp  |     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;

然而，这将不会给出预期的结果，并且下面显示了不正确的输出。

 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

我如何解决这个问题？

注意：此表是根据以前的操作创建的视图。

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

显示与max值匹配的唯一(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)才能支持此功能...