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

  • 时间:
  • 浏览:0
  • 来源:大发uu快3_uu快3娱乐_大发uu快3娱乐

前言

  开心一刻

感觉不妙呀,弟弟舔它! 不该舔的,舔到怀疑人生了......

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', '20190507001', '0507', '影视7班');
INSERT INTO tbl_student_class VALUES ('2', '20190507002', '0507', '影视7班');
INSERT INTO tbl_student_class VALUES ('3', '2019050503', '0508', '影视8班');
INSERT INTO tbl_student_class VALUES ('4', '2019050504', '0508', '影视8班');
INSERT INTO tbl_student_class VALUES ('5', '20190509005', '0509', '影视9班');
INSERT INTO tbl_student_class VALUES ('6', '20190509006', '0509', '影视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

      对于 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

    能正常执行了,本来 一般状况下不推荐原本配置,线上环境往往是“严格模式”,而全是“宽松模式”;确实案例中,无论是“严格模式”,还是“宽松模式”,结果全是对的,那是可能 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 的世界确实是层级分明的等级社会,将低阶概念的属性用在高阶概念上会愿因秩序的混乱,这是不允许的。此时我相信亲戚亲戚亲戚大伙儿都明白:为那此聚合后那么再引用原表中的列 。

单元素集合也是集合

  现在的集合论认为单元素集合是五种正常的集合。单元素集合和空集一样,主本来 为了保持理论的详细性而定义的。本来 对于以集合论为基础的 SQL 来说,当然也须要严格地区分元素和单元素集合。本来 ,元素 a 和集合 {a} 之间处在着非常醒目的层级差别。

  这有另好几次 层级的区别分别对应着 SQL 中的 WHERE 子句和 HAVING 子句的区别。WHERE 子句用于处里"行"你是什么 0 阶的对象,而 HAVING 子句用来处里"集合"你是什么 1 阶的对象。

总结

  1、SQL 严格区分层级,包括谓词逻辑中的层级(EXISTS),也包括集合论中的层级(GROUP BY);

  2、有了层级区分,那么适用于个体上的属性就不适用于团体了,这也本来 为那此聚合查询的 SELECT 子句中那么直接引用原表中的列的愿因;

  3、一般来说,单元素集合的属性和其唯一元素的属性是一样的。你是什么只含有 有另好几次 元素的集合你可不都还能能确实似乎那么必要特意地当成集合来看待,本来 为了保持理论的详细性,亲戚亲戚亲戚大伙儿还是要严格区分元素和单元素集合;

参考

  《SQL基础教程》

  《SQL进阶教程》

猜你喜欢

《飘流幻境》手机版甜蜜七夕即将上线结婚系统

【斗蟹资讯】喂,瞧一瞧,看一看嘞~~~热乎乎的情报,嗯,刚出炉的,据说和臊子面一样酸甜苦是咸emmm...啊呀,我怕是吃到了假的臊子面~~~心塞塞,不过,萝卡情人关系提示,情报

2020-01-18

小米悦米机械键盘Pro静音版开售:599元、TTC静音红轴

IT之家4月25日消息 今天小米商城上线了一款全新的机械键盘——悦米机械键盘Pro静音版。悦米机械键盘Pro静音版采用了CNC全铝机身设计,内置TTC静音红轴,支持背光,共拥有

2020-01-18

中國長城文化學術研討會 助推新時代長城文化事業大發展

探討長城文旅融合創新共繪長城公園建設藍圖大公網訊共議長城保護利用發展大計,共繪長城國家文化公園建設藍圖。10月29日至31日,中國長城文化學術研討會在北京市八達嶺長城腳下——延

2020-01-18

负暄集/秋声裏/赵阳

前天早上醒来,推开窗,一阵秋风带着清新的草丛气息扑面而来,不要探头张望,就知道是小区在进行绿化作业。风裏面带着丝丝凉意,终於同类秋的意思了。我着实我知道距离气象意义上的秋天,还

2020-01-18

美国职业篮球联赛\快艇撞倒鹈鹕五连捷

图:快艇前锋夏利尔双手大力入樽\路透社【大公报讯】NBA常规赛昨天继续上演多场比赛,其中快艇主场对阵鹈鹕,快艇依靠后备奇兵夏利尔独取全场最高的34分,加进去去两名当家球星李奥拿

2020-01-17