为什么 GROUP BY 之后不能直接引用原表中的列？

标准 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与其他数据库服务器一起使用变得更加容易。更多详情请查阅官网：Server SQL Modes。

MySQL 版本不同，内容会略有不同（包括默认值），查阅的时候注意与自身的 MySQL 版本保持一致。

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

语法支持类　　　　

• ONLY_FULL_GROUP_BY

• ANSI_QUOTES

• PIPES_AS_CONCAT

• NO_TABLE_OPTIONS

• NO_AUTO_CREATE_USER

数据检查类　　　

• NO_ZERO_DATE

• NO_ENGINE_SUBSTITUTION

• STRICT_TRANS_TABLES

默认模式

-- 查看 MySQL 版本
SELECT VERSION();

-- 查看 sql_mode
SELECT @@sql_mode;

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

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

阶

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

单元素集合也是集合

a ≠ {a}

总结

参考

《SQL基础教程》
《SQL进阶教程》

作者：青石路
cnblogs.com/youzhibing/p/11516154.html
- END -
最近整理一份面试资料《Java技术栈学习手册》，覆盖了Java技术、面试题精选、Spring全家桶、Nginx、SSM、微服务、数据库、数据结构、架构等等。
获取方式：点“ 在看，关注公众号 Java后端 并回复 777 领取，更多内容陆续奉上。
推荐阅读 
1. 图解 SQL
2. 推荐两个学算法的 GitHub 项目
3. 一条 SQL 引发的事故
4. 贼 TM 好用的 Java 工具类库
5. 推荐 5 款好用的REST API工具
喜欢文章，点个在看