必须出现在GROUP BY子句中或用于聚合函数中 [英] must appear in the GROUP BY clause or be used in an aggregate function

问题描述

我有一个表，看起来像这个调用者'makerar'

  cname | wmname | avg 
 -------- + ------------- + ---------------------- -  
加拿大| zoro | 2.0000000000000000 
西班牙| luffy | 1.00000000000000000000 
西班牙| usopp | 5.0000000000000000 
  

我想为每个cname选择最大平均值。

  SELECT cname，wmname，MAX（avg）FROM makerar GROUP BY cname; 
  

但我会收到一个错误，

 错误：列makerar.wmname必须出现在GROUP BY子句中或用于聚合函数
 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 
 -------- + -------- + ------------------------ 
加拿大| zoro | 2.0000000000000000 
西班牙| luffy | 1.00000000000000000000 
西班牙| usopp | 5.0000000000000000 
  

实际结果应该是

  cname | wmname | max 
 -------- + -------- + ------------------------ 
加拿大| zoro | 2.0000000000000000 
西班牙| usopp | 5.0000000000000000 
  

如何解决此问题？
$ b $注意：这个表是从前一个操作创建的一个VIEW。

解决方案

是的，这是一个常见的聚合问题。在 SQL3（1999）之前，所选字段必须出现在 GROUP BY中子句[*]。

要解决此问题，您必须在子查询中计算聚合，然后将其与自身结合以获取需要显示的其他列：

p>

  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 
 -------- + -------- + ------------------------ 
加拿大| zoro | 2.0000000000000000 
西班牙| usopp | 5.0000000000000000 
  

---

但是你也可以使用窗口函数，看起来比较简单：

  SELECT cname，wmname，MAX（avg）OVER（PARTITION BY cname）AS mx 
 FROM makerar 
; 
  

这个方法唯一的事情是它会显示所有记录（窗口函数不分组）。但它会显示每行中的国家/地区的正确（即最大值为 cname 级别） MAX 给你：

  cname | wmname | mx 
 -------- + -------- + ------------------------ 
加拿大| zoro | 2.0000000000000000 
西班牙| luffy | 5.0000000000000000 
西班牙| usopp | 5.0000000000000000 
  

该解决方案可以说不太优雅，只显示（cname， wmname）匹配最大值的元组是：

  SELECT DISTINCT / * distinct here matter，因为也许有相同最大值的各种元组* * / 
 m.cname，m.wmname，t.avg AS mx 
 FROM（
 SELECT cname，wmname，avg，ROW_NUMBER（）OVER （PARTITION BY avg DESC）AS 
 FROM makerar 
）t JOIN makerar m ON m.cname = t.cname AND m.wmname = t.wmname AND t.rn = 1 
; 
 
 
 cname | wmname | mx 
 -------- + -------- + ------------------------ 
加拿大| zoro | 2.0000000000000000 
西班牙| usopp | 5.0000000000000000 
  

---

[*]：有趣的是，规格排序允许选择非分组字段，主要引擎似乎不太喜欢它。 Oracle和SQLServer根本不允许这样做。默认情况下，Mysql允许使用它，但现在自5.7版本开始，管理员需要在服务器配置中手动启用此选项（ ONLY_FULL_GROUP_BY ），以支持此功能...

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

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;

so i do this

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

Actual Results should be

 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.

解决方案

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
;

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

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

---

[*]: 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屋！