为什么group by 之后不能引用原表的列
标准的SQL规定,在对表进行聚合查询时,只能在select 子句中写下面3种内容:
- 通过group by子句指定的聚合键
- 聚合函数(AVG、COUNT)
- 常量
假设存在以下sql
select aa,bb,count(cc) as count from table1 group by aa;
此时会报错
[Err] 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'table1.bb' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
即 select 列表中的第二个表达式(bb)不在group by的子句中,同时它也不是聚合函数,这与sql模式:ONLY_FULL_GROUP_BY 不相容。
sql模式主要分为「语法支持类」及「数据检查类」
语法支持类
- ONLY_FULL_GROUP_BY
对于GROUP BY 聚合操作,如果在SELECT中的列、HAVING 或者 ORDER BY子句的列,没有在GROUP BY中出现,那么这个sql是不合法的。
- ANSI_QUOTES
启用ANSI_QUOTES后,不能用双引号来引用字符串,因为它被解释为识别符,作用跟`一样。设置它后假设sql:select d from t1 where f="yy"; 会报unknown column `yy` in field list 这样的语法错误。
- PIPES_AS_CONCAT
将 || 视为字符串的连接符而非 “或” 运算符。跟字符串的拼接函数CONCAT() 相类似。
- NO_TABLE_OPTIONS
使用show create table时不会输出mysql特有的语法部分,如 engine,在使用mysqldump跨DB种类迁移的时候需要考虑。
- NO_AUTO_CREATE_USER
不自动创建用户。
数据检查类
- NO_ZERO_DATE
认为日期“0000-00-00”非法,如果设置了严格模式且 insert ignore或update ignore,“0000-00-00”依然允许且只显示warning;如果非严格模式,设置了NO_ZERO_DATE ,“0000-00-00”允许但显示warning。NO_ZERO_IN_DATE控制日期和天是否可为0,即2020-01-00是否合法。
- NO_ENGINE_SUBSTITUTION
使用alter table 或 create table指定engine时,需要的存储引擎被禁用或未编译。启用该模式将会直接抛出错误,不设置此值时,create用默认的存储引擎,alter不进行更改,并抛出warning。
- STRICT_TRANS_TABLES
严格模式,指insert、update出现少值或无效值时该如何处理:
1、把""传给int,严格模式下非法,非严格模式则变为0,产生一个warning;
2、out of range,变成插入最大边界值;
3、插入新行中不包含其定义中没有default子句的非null列的值是,提示该列缺少值。
总结:
- SQL严格区分层级,包括谓词中的层级(EXISTS),也包括集合论中层级(GROUP BY);
- 有了层级区分,那么适用于个体上的属性就不适用于团体了,所以select 子句中不能直接引用原表中的列的原因。
参考:https://mp.weixin.qq.com/s/vT3JCy_PpD8TLB26itRSKQ