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的答案。

在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的)。

这似乎也很有效

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

对我来说，这不是一个“常见的聚合问题”，而只是一个错误的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有同样的“分数”。

在Postgres中，你也可以使用特殊的DISTINCT ON(表达式)语法:

SELECT DISTINCT ON (cname) 
    cname, wmname, avg
FROM 
    makerar 
ORDER BY 
    cname, avg DESC ;

我最近遇到了这个问题，当我试图用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