GROUP BY 后 SELECT 列的限制
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班');
FROM tbl_student_class
GROUP BY cno,cname;
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
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
默认模式
SELECT VERSION();
-- 查看 sql_mode
SELECT @@sql_mode;
SELECT cno,cname,count(sno),MAX(sno)
FROM tbl_student_class
GROUP BY cno;
阶
为什么聚合后不能再引用原表中的列
单元素集合也是集合
总结
参考
《SQL基础教程》
《SQL进阶教程》
作者:青石路
原文:cnblogs.com/youzhibing/p/11516154.html