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

小开

最佳答案

是的，这是一个常见的聚合问题。在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
;

这个方法唯一的特点是它将显示所有记录(窗口函数不分组)。但是它会在每一行中显示国家的正确MAX(即最大cname级别)，所以这取决于你:

 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)，以支持此功能…

小开

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;

请注意

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

小开

在Postgres中，你也可以使用特殊的“> < a href = " http://www.postgresql.org/docs/9.3/static/sql-select.html SQL-DISTINCT DISTINCT ON (expression) < / >语法:

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

小开

在group by select中指定非分组和非聚合字段的问题是，引擎无法知道在这种情况下它应该返回哪个记录的字段。是第一次吗?是最后一次吗?通常没有与聚合结果自然对应的记录(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

小开

这似乎也很有效

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;时，你期望得到哪个结果?应该是spain+luffy还是spain+usopp?为什么?如果有几个合适的wmname，在查询中如何选择“更好的”wmname并不是确定，因此结果也不确定。这就是为什么SQL解释器返回一个错误-查询是不正确的。

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