神奇的SQL之层级 → 为什么GROUP BY之后不能直接引用原表中的列

Java精选 2022-08-09

收录于合集 #MySQL 84个

GROUP BY后SELECT列的限制

标准SQL规定，在对表进行聚合查询的时候，只能在SELECT子句中写下面 3 种内容：

通过GROUP BY子句指定的聚合键、聚合函数（SUM 、AVG 等）、常量。我们来看个例子。

我们有学生班级表（tbl_student_class）以及数据如下：

DROP TABLE IF EXISTS tbl_student_class;
CREATE TABLE tbl_student_class (
  id int(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  sno varchar(12) NOT NULL COMMENT '学号',
  cno varchar(5) NOT NULL COMMENT '班级号',
  cname varchar(20) NOT NULL COMMENT '班级名',
  PRIMARY KEY (id)
) COMMENT='学生班级表';

-- ----------------------------
-- Records of tbl_student_class
-- ----------------------------
INSERT INTO tbl_student_class VALUES ('1', '20190607001', '0607', '影视7班');
INSERT INTO tbl_student_class VALUES ('2', '20190607002', '0607', '影视7班');
INSERT INTO tbl_student_class VALUES ('3', '20190608003', '0608', '影视8班');
INSERT INTO tbl_student_class VALUES ('4', '20190608004', '0608', '影视8班');
INSERT INTO tbl_student_class VALUES ('5', '20190609005', '0609', '影视9班');
INSERT INTO tbl_student_class VALUES ('6', '20190609006', '0609', '影视9班');

我们想统计各个班（班级号、班级名）一个有多少人、以及最大的学号，我们该怎么写这个查询SQL？我想大家应该都会

SELECT cno,cname,count(sno),MAX(sno) 
FROM tbl_student_class
GROUP BY cno,cname;

可是有人会想了，cno和cname本来就是一对一，cno一旦确定，cname也就确定了，那SQL是不是可以这么写？

SELECT cno,cname,count(sno),MAX(sno) 
FROM tbl_student_class
GROUP BY cno;

执行报错了：

[Err] 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.tbl_student_class.cname' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

提示信息：

SELECT列表中的第二个表达式（cname）不在GROUP BY的子句中，同时它也不是聚合函数；这与SQL模式：ONLY_FULL_GROUP_BY不相容。

为什么GROUP BY之后不能直接引用原表（不在GROUP BY子句）中的列？莫急，我们慢慢往下看。

SQL模式

MySQL 服务器可以在不同的SQL模式下运行，并且可以针对不同的客户端以不同的方式应用这些模式，具体取决于sql_mode系统变量的值。DBA可以设置全局SQL模式以匹配站点服务器操作要求，并且每个应用程序可以将其会话SQL模式设置为其自己的要求。

模式会影响 MySQL支持的SQL语法以及它执行的数据验证检查，这使得在不同环境中使用MySQL以及将MySQL与其他数据库服务器一起使用变得更加容易。

更多详情请查阅官网：ServerSQLModes。MySQL版本不同，内容会略有不同（包括默认值），查阅的时候注意与自身的 MySQL版本保持一致。

SQL 模式主要分两类：语法支持类和数据检查类，常用的如下

语法支持类

ONLY_FULL_GROUP_BY

对于GROUP BY聚合操作，如果在 SELECT 中的列、HAVING或者ORDER BY子句的列，没有在GROUP BY中出现，那么这个SQL是不合法的。

ANSI_QUOTES

启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符，作用与 ` 一样。设置它以后，update t set f1="" …，会报Unknown column ‘’ in field list 这样的语法错误。

PIPES_AS_CONCAT

将 || 视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样的，也和字符串的拼接函数CONCAT() 相类似。

NO_TABLE_OPTIONS

使用SHOW CREATE TABLE时不会输出MySQL特有的语法部分，如ENGINE ，这个在使用mysqldump跨DB种类迁移的时候需要考虑。

NO_AUTO_CREATE_USER

字面意思不自动创建用户。在给MySQL用户授权时，我们习惯使用GRANT … ON … TO dbuser顺道一起创建用户。设置该选项后就与oracle操作类似，授权之前必须先建立用户。

数据检查类

NO_ZERO_DATE

认为日期 ‘0000-00-00’ 非法，与是否设置后面的严格模式有关

1、如果设置了严格模式，则NO_ZERO_DATE 自然满足。但如果是INSERT IGNORE或UPDATE IGNORE，’0000-00-00’依然允许且只显示warning；

2、如果在非严格模式下，设置了NO_ZERO_DATE，效果与上面一样，’0000-00-00’ 允许但显示warning；如果没有设置NO_ZERO_DATE，no warning，当做完全合法的值；

3、NO_ZERO_IN_DATE情况与上面类似，不同的是控制日期和天，是否可为 0 ，即 2010-01-00 是否合法；

NO_ENGINE_SUBSTITUTION

使用 ALTER TABLE或CREATE TABLE指定ENGINE时，需要的存储引擎被禁用或未编译，该如何处理。启用NO_ENGINE_SUBSTITUTION 时，那么直接抛出错误；不设置此值时，CREATE用默认的存储引擎替代，ATLER不进行更改，并抛出一个warning

STRICT_TRANS_TABLES

设置它，表示启用严格模式。注意STRICT_TRANS_TABLES不是几种策略的组合，单独指 INSERT、UPDATE出现少值或无效值该如何处理：

1、前面提到的把 ‘’ 传给int，严格模式下非法，若启用非严格模式则变成 0，产生一个warning；

2、Out Of Range，变成插入最大边界值；

3、当要插入的新行中，不包含其定义中没有显式DEFAULT子句的非NULL列的值时，该列缺少值；

默认模式

当我们没有修改配置文件的情况下，MySQL是有自己的默认模式的；版本不同，默认模式也不同

– 查看 MySQL 版本
SELECT VERSION();
– 查看 sql_mode
SELECT @@sql_mode;

我们可以看到，5.7.21的默认模式包含：

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

而第一个：ONLY_FULL_GROUP_BY 就会约束：当我们进行聚合查询的时候，SELECT 的列不能直接包含非 GROUP BY 子句中的列。那如果我们去掉该模式（从“严格模式”到“宽松模式”）呢？

我们发现，上述报错的 SQL

-- 宽松模式下 可以执行
SELECT cno,cname,count(sno),MAX(sno) 
FROM tbl_student_class
GROUP BY cno;

能正常执行了，但是一般情况下不推荐这样配置，线上环境往往是“严格模式”，而不是“宽松模式”；虽然案例中，无论是“严格模式”，还是“宽松模式”，结果都是对的，那是因为cno与cname唯一对应的，如果cno与cname不是唯一对应，那么在“宽松模式下”cname的值是随机的，这就会造成难以排查的问题，有兴趣的可以去试试。那为什么会有 ONLY_FULL_GROUP_BY 模式呢？我们继续往下看

阶

阶（order）是用来区分集合或谓词的阶数的概念。谓词逻辑中，根据输入值的阶数对谓词进行分类。= 或者BETWEEEN等输入值为一行的谓词叫作"一阶谓词"，而像EXISTS这样输入值为行的集合的谓词叫作"二阶谓词"（HAVING的输入值也是集合，但它不是谓词）。

以此类推，三阶谓词＝输入值为"集合的集合"的谓词，四阶谓词＝输入值为"集合的集合的集合"的谓词，但是SQL里并不会出现三阶以上的情况，所以不用太在意。简单点如下图

谈到了阶，就不得不谈下集合论；集合论是SQL语言的根基，因为它的这个特性，SQL也被称为面向集合语言。只有从集合的角度来思考，才能明白SQL的强大威力。通过上图，相信大家也都能看到，这里不做更深入的讲解了，有兴趣的可以去查相关资料。

为什么聚合后不能再引用原表中的列

很多人都知道聚合查询的限制，但是很少有人能正确地理解为什么会有这样的约束。

表 tbl_student_class 中的 cname 存储的是每位学生的班级信息，但需要注意的是，这里的 cname 只是每个学生的属性，并不是小组的属性，而GROUP BY又是聚合操作，操作的对象就是由多个学生组成的小组，因此，小组的属性只能是平均或者总和等统计性质的属性，如下图

询问每个学生的cname是可以的，但是询问由多个学生组成的小组的cname 就没有意义了。

对于小组来说，只有"一共多少学生"或者"最大学号是多少？“这样的问法才是有意义的。强行将适用于个体的属性套用于团体之上，纯粹是一种分类错误；而 GROUP BY的作用是将一个个元素划分成若干个子集，使用GROUP BY聚合之后，SQL 的操作对象便由 0 阶的"行"变为了 1 阶的"行的集合”，此时，行的属性便不能使用了。

SQL 的世界其实是层级分明的等级社会，将低阶概念的属性用在高阶概念上会导致秩序的混乱，这是不允许的。此时我相信大家都明白：为什么聚合后不能再引用原表中的列。