必须出现在 GROUP BY 子句中或在聚合函数中使用 [英] must appear in the GROUP BY clause or be used in an aggregate function
问题描述
我有一张桌子看起来像这个来电者makerar"
I have a table that looks like this caller 'makerar'
cname | wmname | avg
--------+-------------+------------------------
canada | zoro | 2.0000000000000000
spain | luffy | 1.00000000000000000000
spain | usopp | 5.0000000000000000
我想为每个 cname 选择最大平均值.
And I want to select the maximum avg for each cname.
SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname;
但是我会得到一个错误，
but I will get an error,
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;
然而，这不会给出预期的结果，下面会显示不正确的输出.
however this will not give the intented results, and the incorrect output below is shown.
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
我该如何解决这个问题?
How can I go about fixing this issue?
注意:此表是从之前的操作中创建的视图.
Note: This table is a VIEW created from a previous operation.
推荐答案
是的，这是一个常见的聚合问题.在SQL3 (1999)之前，所选字段必须出现在GROUP BY 条款[*].
Yes, this is a common aggregation problem. Before SQL3 (1999), the selected fields must appear in the GROUP BY clause[*].
要解决此问题，您必须在子查询中计算聚合，然后将其与自身连接以获得需要显示的其他列:
To workaround this issue, you must calculate the aggregate in a sub-query and then join it with itself to get the additional columns you'd need to show:
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
<小时>
但是你也可以使用窗口函数，它看起来更简单:
But you may also use window functions, which looks simpler:
SELECT cname, wmname, MAX(avg) OVER (PARTITION BY cname) AS mx
FROM makerar
;
这个方法唯一的一点就是它会显示所有的记录(窗口函数不分组).但它会在每一行中显示国家/地区的正确(即在 cname 级别达到最大值)MAX，所以这取决于您:
The only thing with this method is that it will show all records (window functions do not group). But it will show the correct (i.e. maxed at cname level) MAX for the country in each row, so it's up to you:
cname | wmname | mx
--------+--------+------------------------
canada | zoro | 2.0000000000000000
spain | luffy | 5.0000000000000000
spain | usopp | 5.0000000000000000
显示唯一与最大值匹配的 (cname, wmname) 元组的解决方案可能不太优雅，是:
The solution, arguably less elegant, to show the only (cname, wmname) tuples matching the max value, is:
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)才能支持此功能...
[*]: Interestingly enough, even though the spec sort of allows to select non-grouped fields, major engines seem to not really like it. Oracle and SQLServer just don't allow this at all. Mysql used to allow it by default, but now since 5.7 the administrator needs to enable this option (ONLY_FULL_GROUP_BY) manually in the server configuration for this feature to be supported...
这篇关于必须出现在 GROUP BY 子句中或在聚合函数中使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
