group by 到底是什麼妖怪？
- 2019 年 12 月 18 日
- 筆記
本來今天是要接著整理日誌系統的優化的，但是自己對梳理一些名詞理解有些欠缺，思來想去還是想討論下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 maxage from 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的欄位會隨機返回一條，如果語義無錯，就只能是邏輯錯誤了，所以難道我一開始舉的例子並不正確？
本篇文章需要大家一起討論驗證，再項目中本條語法並沒有幾條是遵守的，並沒有出現數據不準確問題。
