在group by selects中指定非分组和非聚合字段的问题是，引擎无法知道在这种情况下应该返回哪个记录的字段。是第一次吗?是最后一次吗?通常没有与聚合结果自然对应的记录(min和max是例外)。

但是，有一种变通办法:也聚合所需的字段。 在postgres中，这应该是有效的:

SELECT cname, (array_agg(wmname ORDER BY avg DESC))[1], MAX(avg)
FROM makerar GROUP BY cname;

注意，这将创建一个包含所有wname的数组，以avg排序，并返回第一个元素(postgres中的数组是基于1的)。

我最近遇到了这个问题，当我试图用case when计数时，发现改变which和count语句的顺序可以解决这个问题:

SELECT date(dateday) as pick_day,
COUNT(CASE WHEN (apples = 'TRUE' OR oranges 'TRUE') THEN fruit END)  AS fruit_counter

FROM pickings

GROUP BY 1

而不是使用-在后者中，我得到了apple和orange应该出现在聚合函数中的错误

CASE WHEN ((apples = 'TRUE' OR oranges 'TRUE') THEN COUNT(*) END) END AS fruit_counter

是的，这是一个常见的聚合问题。在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)来支持这个特性…

这似乎也很有效

SELECT *
FROM makerar m1
WHERE m1.avg = (SELECT MAX(avg)
                FROM makerar m2
                WHERE m1.cname = m2.cname
               )

SELECT t1.cname, t1.wmname, t2.max
FROM makerar t1 JOIN (
    SELECT cname, MAX(avg) max
    FROM makerar
    GROUP BY cname ) t2
ON t1.cname = t2.cname AND t1.avg = t2.max;

使用rank()窗口函数:

SELECT cname, wmname, avg
FROM (
    SELECT cname, wmname, avg, rank() 
    OVER (PARTITION BY cname ORDER BY avg DESC)
    FROM makerar) t
WHERE rank = 1;

Note

每个组都保留多个最大值。如果你想要每组只有一条记录，即使avg等于max的记录不止一条，你应该检查@ypercube的答案。

对我来说，这不是一个“常见的聚合问题”，而只是一个错误的SQL查询。“为每个cname选择最大平均值…”的唯一正确答案是

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

结果将是:

 cname  |      MAX(avg)
--------+---------------------
 canada | 2.0000000000000000
 spain  | 5.0000000000000000

这个结果大体上回答了“每个组的最佳结果是什么?”的问题。我们看到，西班牙最好的结果是5，加拿大最好的结果是2。这是真的，没有错误。 如果我们还需要显示wmname，我们必须回答这个问题:“从结果集中选择wmname的规则是什么?”让我们稍微改变一下输入数据来澄清这个错误:

  cname | wmname |        avg           
--------+--------+-----------------------
 spain  | zoro   |  1.0000000000000000
 spain  | luffy  |  5.0000000000000000
 spain  | usopp  |  5.0000000000000000

SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname;?应该是西班牙+路飞还是西班牙+乌索普?为什么?如果有几个合适的wmname，查询中没有确定如何选择“更好的”wmname，因此结果也没有确定。这就是为什么SQL解释器返回一个错误-查询是不正确的。

换句话说，“谁是西班牙组最好的?”这个问题没有正确答案。路飞不比usopp好，因为usopp有同样的“分数”。