1. group by的常規用法
group by的常規用法是配合聚合函數,利用分組信息進行統計,常見的是配合max等聚合函數篩選數據後分析,以及配合having進行篩選後過濾。
假設現有資料庫表如下:
表user_info,id主鍵,user_id唯一鍵
CREATE TABLE `user_info` ( `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵id', `user_id` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '用戶編號', `grade` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '年級', `class` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '班級', PRIMARY KEY (`id`), UNIQUE INDEX `uniq_user_id` (`user_id`) ) ENGINE=InnoDB
數據
INSERT INTO `user_info` (`id`, `user_id`, `grade`, `class`) VALUES (10, '10230', 'C', 'B'); INSERT INTO `user_info` (`id`, `user_id`, `grade`, `class`) VALUES (9, '10229', 'C', 'a'); INSERT INTO `user_info` (`id`, `user_id`, `grade`, `class`) VALUES (8, '10228', 'B', 'b'); INSERT INTO `user_info` (`id`, `user_id`, `grade`, `class`) VALUES (7, '10227', 'B', 'b'); INSERT INTO `user_info` (`id`, `user_id`, `grade`, `class`) VALUES (6, '10226', 'B', 'a'); INSERT INTO `user_info` (`id`, `user_id`, `grade`, `class`) VALUES (5, '10225', 'B', 'a'); INSERT INTO `user_info` (`id`, `user_id`, `grade`, `class`) VALUES (4, '10224', 'A', 'b'); INSERT INTO `user_info` (`id`, `user_id`, `grade`, `class`) VALUES (3, '10223', 'A', 'b'); INSERT INTO `user_info` (`id`, `user_id`, `grade`, `class`) VALUES (2, '10222', 'A', 'a'); INSERT INTO `user_info` (`id`, `user_id`, `grade`, `class`) VALUES (1, '10221', 'A', 'a');
id | user_id | grade | class |
---|---|---|---|
1 | 10221 | A | a |
2 | 10222 | A | a |
3 | 10223 | A | b |
4 | 10224 | A | b |
5 | 10225 | B | a |
6 | 10226 | B | a |
7 | 10227 | B | b |
8 | 10228 | B | b |
9 | 10229 | C | a |
10 | 10230 | C | b |
聚合函數max
select max(user_id),grade from user_info group by grade ;
結果
max(user_id) | grade |
---|---|
10224 | A |
10228 | B |
10230 | C |
這條sql的含義很明確,將數據按照grade字段分組,查詢每組最大的user_id以及當前組內容。註意,這裡分組條件是grade,查詢的非聚合條件也是grade。這裡不產生沖突。
having
select max(user_id),grade from user_info group by grade having grade>'A'
結果
max(user_id) | grade |
---|---|
10228 | B |
10230 | C |
這條sql與上面例子中的基本相同,不過後面跟瞭having過濾條件。將grade不滿足’>A’的過濾掉瞭。註意,這裡分組條件是grade,查詢的非聚合條件也是grade。這裡不產生沖突。
2. group by的非常規用法
select max(user_id),id,grade from user_info group by grade
結果
max(user_id) | id | grade |
---|---|---|
10224 | 1 | A |
10228 | 5 | B |
10230 | 9 | C |
這條sql的結果就值得討論瞭,與上述例子不同的是,查詢條件多瞭id一列。數據按照grade分組後,grade一列是相同的,max(user_id)按照數據進行計算也是唯一的,id一列是如何取值的?看上述的數據結果,
推論:id是物理內存的第一個匹配項。
究竟是與不是需要繼續探討。
修改數據
修改id按照上述數據結果,將id=1,改為id=99,執行sql後結論:
max(user_id) | id | grade |
---|---|---|
10224 | 2 | A |
10228 | 5 | B |
10230 | 9 | C |
顯然,與上述例子的結果不同。第一條數據id變成瞭99,查出的結果第一條數據的id從1變成瞭2。表明,id這個非聚合條件字段的取值與數據寫入的時間無關,因為id=1的記錄是先於id=2存在的,修改的數據不過是修改瞭這條數據的內容。結合mysql的數據存儲理論,由於id是主鍵,所以數據在檢索是是按照主鍵排序後進行過濾的,因此
推論:id字段的選取是按照mysql存儲的檢索數據匹配的第一條。
將id改為1後恢復瞭原始結果,無法推翻上述推論。
更改查詢條件
select max(user_id),user_id,id,grade from user_info group by grade
1
max(user_id) | user_id | id | grade |
---|---|---|---|
10224 | 10221 | 1 | A |
10228 | 10225 | 5 | B |
10230 | 10229 | 9 | C |
將數據user_id改為10999後,執行結果為
max(user_id) | user_id | id | grade |
---|---|---|---|
10224 | 10999 | 1 | A |
10228 | 10225 | 5 | B |
10230 | 10229 | 9 | C |
修改瞭user_id後,並沒有改變查詢到的數據條目,因此得出修改唯一鍵並不能影響查詢匹配的條目規則,所以條目規則依然是匹配第一條,即id=1。
結論
當group by 與聚合函數配合使用時,功能為分組後計算
當group by 與having配合使用時,功能為分組後過濾
當group by 與聚合函數,同時非聚合字段同時使用時,非聚合字段的取值是第一個匹配到的字段內容,即id小的條目對應的字段內容。