选定的项不必出现在GROUP子句中，也不必用于聚合函数

Question

据我所知，在sql/mysql中，select子句中的项必须出现在GROUP BY子句中，或者在聚合函数中使用，就像在这里中一样。

然而，下面的例子可能改变了我的想法。

两个表：学生(sid是关键)

sid  | name | email
========================
99901| mike | mike@a.edu
99902| jane | jane@b.edu
99903| peter| pete@b.edu

采取(sid+oid在一起是关键，表示提供id)

sid  | oid| grade
==================
99901| 1  | 100
99901| 2  | 30
99901| 3  | 40
99902| 4  | 100
99902| 5  | 100
99902| 6  | 40
99903| 6  | 95

问题：我想找出每个至少上过两门课的学生的sid、姓名和平均成绩。

答案

select s.sid, name, avg(grade) as average
from Student as s, Took as t
where s.sid = t.sid
group by s.sid
having count(*) >= 2;

结果：

sid  | name | avgerage
=======================
99901| mike | 56.6667
99902| jane | 80.0000

基于必须出现在GROUP子句中或在聚合函数中使用，查询应该是不正确的，因为name既不是group子句，也不是聚合函数。

我查看了一些posts和这，我的理解是，虽然name既不是group子句，也不是聚合函数，但我们按sid (键)分组，每个sid只对应一个name，因此它不会返回多个选项，其中sql不知道返回哪个选项。为了证实我的理解，如果我多选择一列email，它仍然可以；但是如果选择oid，则会产生错误，因为每个sid对应于多个oid。

如果我的理解是错误的，有人能纠正我的理解吗?或者更详细地阐述这句话：must appear in the GROUP BY clause or be used in an aggregate function

谢谢。

第一编辑

顺便说一下，我在MySQL 8.0.17中进行了测试

第二版

当你阅读下面的答案/评论时，只是一个有用链接的摘要。

功能性跛行

SQL标准变更

Answer 1

你被正确地教导了。

根据Standard，当您使用GROUP BY时，可以出现在SELECT子句中的列分为三类：

1. GROUP BY子句中包含的列。在本例中，您有s.sid。
2. 聚集列。在本例中，您有avg(grade)。
3. 由于s.sid是表的PK，所以可以在不聚合它的情况下包含s.name。

一切都很好。

但是，您应该知道MySQL 5.7.4和更高版本确实允许您以非聚合形式包含其他列。这是MySQL的一个bug/特性，我个人认为它很容易出错。如果您这样做，MySQL将悄悄地随机选择一个值，而不聚合它，也不告诉您。

这个功能可以通过在较新版本的ONLY_FULL_GROUP_BY中使用MySQL配置参数(如@Shawn在注释中所指出的那样)来启用，从而允许运行旧的/坏的查询。不过我会尽量避免使用它。

Answer 2

首先，您应该使用正确、显式的JOIN语法：

select s.sid, s.name, avg(grade) as average
from Student s join
     Took t
     on s.sid = t.sid
group by s.sid
having count(*) >= 2;

这是因为一种叫做函数依赖的东西。基本上，这是标准的一部分:如果您group by一个主键或唯一键，那么您可以从该表中包含任何列。

这里是关于这个主题的文档。

也就是说，因为数据库知道s.sid是唯一的，所以使用其他列是安全的。这是标准的一部分。我所知道的唯一支持此功能的其他公共数据库是Postgres。