group by 到底是什么妖怪？
本来今天是要接着整理日志系统的优化的，但是自己对梳理一些名词理解有些欠缺，思来想去还是想讨论下group by语法问题，这个问题我以前举例说明过，脑海里就一致这么认为:在SELECT 列表中所有未包含在组函数中的列都应该是包含在 GROUP BY 子句中的。
验证：
-- ------------------------------ Table structure for mytbl2-- ----------------------------DROP TABLE IF EXISTS `mytbl2`;CREATE TABLE `mytbl2` ( `id` int(11) NULL DEFAULT NULL, `NAME` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `age` int(11) NULL DEFAULT NULL, `dept` int(11) NULL DEFAULT NULL) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ------------------------------ Records of mytbl2-- ----------------------------INSERT INTO `mytbl2` VALUES (1, 'zhangsan', 33, 101);INSERT INTO `mytbl2` VALUES (2, 'li4', 34, 101);INSERT INTO `mytbl2` VALUES (3, 'w5', 34, 102);INSERT INTO `mytbl2` VALUES (4, 'zhao6', 34, 102);INSERT INTO `mytbl2` VALUES (5, 't7', 36, 102);目的：查询表中年龄最大的员工部门，名字
select `NAME`,dept,MAX(age)from mytbl2 GROUP BY dept;
此条sql看上去语义正确，但逻辑却错误，结果与预想不一致
分析：先找出表中最大年龄，作为临时表，再联查
SELECT *FROM mytbl2 m INNER JOIN (select dept,MAX(age) as maxagefrom mytbl2 GROUP BY dept)ab on ab.dept=m.dept and m.age=ab.maxage;
当然本表因为特殊表，存在单标自关联
1.语法为什么不会报错？
在高版本sql中，本语法是不会报错的
2.但是到底group by语法是怎样的？
查询官网：
https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html一开始我考虑是不是版本问题，但实际5.7和8.0是针对此项毫无分别的
It is also possible to have more than one nonaggregate column in the SELECT list when employing ONLY_FULL_GROUP_BY. In this case, every such column must be limited to a single value in the WHERE clause, and all such limiting conditions must be joined by logical AND, as shown here:在改变sql_mode的情况下，在WHERE子句中，每一个这样的列必须限制为一个值，并且所有这样的限制条件必须由逻辑and连接
正常的sql语句还是select与group by后的字段一致，或者后面跟随函数操作。官网知识提到了sql版本高了语法不会报错，在改变sql_mode的情况下，此语法错误，官网第一句话
SQL-92 and earlier does not permit queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are not named in the GROUP BYclause. For example, this query is illegal in standard SQL-92 because the nonaggregated name column in the select list does not appear in the GROUP BY:
示例：
SELECT o.custid, c.name, MAX(o.payment) FROM orders AS o, customers AS c WHERE o.custid = c.custid GROUP BY o.custid;group by 的是custid，但是select字段除函数操作外多了个name，this query is illegal，但只强调SQL-92版本，我以为是版本问题，但是SQL8.0与5.7一模一样啊，并没有找到高版本怎么解决这个问题
说起来这个sql_mode吧，我以前的例子提到过改变sql_mode,再执行语法是报错的
设定sql模式 set sql_mode='ONLY_FULL_GROUP_BY';
但其实这样还是不能证明这个问题，因为改动了sql_mode,
群里也讨论了
直到现在我还是不能理解此语法问题，只能说特殊场景会出现数据结果不准确问题，网上说，mysql进行了优化，没有group by的字段会随机返回一条，如果语义无错，就只能是逻辑错误了，所以难道我一开始举的例子并不正确？
本篇文章需要大家一起讨论验证，再项目中本条语法并没有几条是遵守的，并没有出现数据不准确问题。
- 点赞
- 收藏
- 关注作者
评论（0）