点击关注上方“SQL数据库开发”,
设为“置顶或星标”,第一时间送达干货
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与其他数据库服务器一起使用变得更加容易。更多详情请查阅官网: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——
后台回复关键字:1024,获取一份精心整理的技术干货 后台回复关键字:进群,带你进入高手如云的交流群。 推荐阅读 这是一个能学到技术的公众号,欢迎关注
点击「阅读原文」了解SQL训练营