分組查詢
語法：
SELECT column, group_function,... FROM table[WHERE condition]GROUP BY group_by_expression[HAVING group_condition];1234
說明：
group_function：聚合函式。
group_by_expression：分組表示式，多個之間用逗號隔開。
group_condition：分組之後對資料進行過濾。
分組中，select後面只能有兩種型別的列：
-
出現在group by後的列
-
或者使用聚合函式的列
聚合函式
| 函式名稱 |
作用 |
| max |
查詢指定列的最大值 |
| min |
查詢指定列的最小值 |
| count |
統計查詢結果的行數 |
| sum |
求和，返回指定列的總和 |
| avg |
求平均值，返回指定列資料的平均值 |
分組時，可以使用使用上面的聚合函式。
準備資料
drop table if exists t_order;-- 建立訂單表create table t_order(
id int not null AUTO_INCREMENT COMMENT '訂單id',
user_id bigint not null comment '下單人id',
user_name varchar(16) not null default '' comment '使用者名稱',
price decimal(10,2) not null default 0 comment '訂單金額',
the_year SMALLINT not null comment '訂單建立年份',
PRIMARY KEY (id)) comment '訂單表';-- 插入資料insert into t_order(user_id,user_name,price,the_year) values
(1001,'路人甲Java',11.11,'2017'),
(1001,'路人甲Java',22.22,'2018'),
(1001,'路人甲Java',88.88,'2018'),
(1002,'劉德華',33.33,'2018'),
(1002,'劉德華',12.22,'2018'),
(1002,'劉德華',16.66,'2018'),
(1002,'劉德華',44.44,'2019'),
(1003,'張學友',55.55,'2018'),
(1003,'張學友',66.66,'2019');mysql> select * from t_order;+----+---------+---------------+-------+----------+| id | user_id | user_name | price | the_year |+----+---------+---------------+-------+----------+| 1 | 1001 | 路人甲Java | 11.11 | 2017 || 2 | 1001 | 路人甲Java | 22.22 | 2018 || 3 | 1001 | 路人甲Java | 88.88 | 2018 || 4 | 1002 | 劉德華 | 33.33 | 2018 || 5 | 1002 | 劉德華 | 12.22 | 2018 || 6 | 1002 | 劉德華 | 16.66 | 2018 || 7 | 1002 | 劉德華 | 44.44 | 2019 || 8 | 1003 | 張學友 | 55.55 | 2018 || 9 | 1003 | 張學友 | 66.66 | 2019 |+----+---------+---------------+-------+----------+9 rows in set (0.00 sec)1234567891011121314151617181920212223242526272829303132333435363738
單欄位分組
http://dxb.myzx.cn/cure/
需求：查詢每個使用者下單數量，輸出：使用者id、下單數量，如下：
mysql> SELECT
user_id 使用者id, COUNT(id) 下單數量 FROM
t_order GROUP BY user_id;+----------+--------------+| 使用者id | 下單數量 |+----------+--------------+| 1001 | 3 || 1002 | 4 || 1003 | 2 |+----------+--------------+3 rows in set (0.00 sec)12345678910111213
多欄位分組
需求：查詢每個使用者每年下單數量，輸出欄位：使用者id、年份、下單數量，如下：
mysql> SELECT
user_id 使用者id, the_year 年份, COUNT(id) 下單數量 FROM
t_order GROUP BY user_id , the_year;+----------+--------+--------------+| 使用者id | 年份 | 下單數量 |+----------+--------+--------------+| 1001 | 2017 | 1 || 1001 | 2018 | 2 || 1002 | 2018 | 3 || 1002 | 2019 | 1 || 1003 | 2018 | 1 || 1003 | 2019 | 1 |+----------+--------+--------------+6 rows in set (0.00 sec)12345678910111213141516
分組前篩選資料
分組前對資料進行篩選，使用where關鍵字
需求：需要查詢2018年每個使用者下單數量，輸出：使用者id、下單數量，如下：
mysql> SELECT
user_id 使用者id, COUNT(id) 下單數量 FROM
t_order t WHERE
t.the_year = 2018
GROUP BY user_id;+----------+--------------+| 使用者id | 下單數量 |+----------+--------------+| 1001 | 2 || 1002 | 3 || 1003 | 1 |+----------+--------------+3 rows in set (0.00 sec)123456789101112131415
分組後篩選資料
分組後對資料篩選，使用having關鍵字
**需求：**查詢2018年訂單數量大於1的使用者，輸出：使用者id，下單數量，如下：
方式1：
mysql> SELECT
user_id 使用者id, COUNT(id) 下單數量 FROM
t_order t WHERE
t.the_year = 2018
GROUP BY user_id HAVING count(id)>=2;+----------+--------------+| 使用者id | 下單數量 |+----------+--------------+| 1001 | 2 || 1002 | 3 |+----------+--------------+2 rows in set (0.00 sec)123456789101112131415
方式2：
mysql> SELECT
user_id 使用者id, count(id) 下單數量 FROM
t_order t WHERE
t.the_year = 2018
GROUP BY user_id HAVING 下單數量>=2;+----------+--------------+| 使用者id | 下單數量 |+----------+--------------+| 1001 | 2 || 1002 | 3 |+----------+--------------+2 rows in set (0.00 sec)123456789101112131415
where和having的區別
where是在分組（聚合）前對記錄進行篩選，而having是在分組結束後的結果裡篩選，最後返回整個sql的查詢結果。
可以把having理解為兩級查詢，即含having的查詢操作先獲得不含having子句時的sql查詢結果表，然後在這個結果表上使用having條件篩選出符合的記錄，最後返回這些記錄，因此，having後是可以跟聚合函式的，並且這個聚集函式不必與select後面的聚集函式相同。
分組後排序
**需求：**獲取每個使用者最大金額，然後按照最大金額倒序，輸出：使用者id，最大金額，如下：
mysql> SELECT
user_id 使用者id, max(price) 最大金額 FROM
t_order t GROUP BY user_id ORDER BY 最大金額 desc;+----------+--------------+| 使用者id | 最大金額 |+----------+--------------+| 1001 | 88.88 || 1003 | 66.66 || 1002 | 44.44 |+----------+--------------+3 rows in set (0.00 sec)1234567891011121314
where & group by & having & order by & limit 一起協作
where、group by、having、order by、limit這些關鍵字一起使用時，先後順序有明確的限制，語法如下：
select 列 from 表名where [查詢條件]group by [分組表示式]having [分組過濾條件]order by [排序條件]limit [offset,] count;1234567
注意：
寫法上面必須按照上面的順序來寫。
示例：
需求：查詢出2018年，下單數量大於等於2的，按照下單數量降序排序，最後只輸出第1條記錄，顯示：使用者id，下單數量，如下：
mysql> SELECT
user_id 使用者id, COUNT(id) 下單數量 FROM
t_order t WHERE
t.the_year = 2018
GROUP BY user_id HAVING count(id)>=2
ORDER BY 下單數量 DESC
LIMIT 1;+----------+--------------+| 使用者id | 下單數量 |+----------+--------------+| 1002 | 3 |+----------+--------------+1 row in set (0.00 sec)12345678910111213141516
mysql分組中的坑
本文開頭有介紹，分組中select後面的列只能有2種：
-
出現在group by後面的列
-
使用聚合函式的列
oracle、sqlserver、db2中也是按照這種規範來的。
文中使用的是5.7版本，預設是按照這種規範來的。
mysql早期的一些版本，沒有上面這些要求，select後面可以跟任何合法的列。
示例
需求：獲取每個使用者下單的最大金額及下單的年份，輸出：使用者id，最大金額，年份，寫法如下：
mysql> select
user_id 使用者id, max(price) 最大金額, the_year 年份 FROM t_order t GROUP BY t.user_id;ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'javacode2018.t.the_year' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by12345
上面的sql報錯了，原因因為the_year不符合上面說的2條規則（select後面的列必須出現在group by中或者使用聚合函式），而sql_mode限制了這種規則，我們看一下sql_mode的配置：
mysql> select @@sql_mode;+-------------------------------------------------------------------------------------------------------------------------------------------+| @@sql_mode |+-------------------------------------------------------------------------------------------------------------------------------------------+| 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 |+-------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)1234567
sql_mode中包含了ONLY_FULL_GROUP_BY，這個表示select後面的列必須符合上面的說的2點規範。
可以將ONLY_FULL_GROUP_BY去掉，select後面就可以加任意列了，我們來看一下效果。
修改mysql中的my.ini檔案：
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION1
重啟mysql，再次執行，效果如下：
mysql> select
user_id 使用者id, max(price) 最大金額, the_year 年份 FROM t_order t GROUP BY t.user_id;+----------+--------------+--------+| 使用者id | 最大金額 | 年份 |+----------+--------------+--------+| 1001 | 88.88 | 2017 || 1002 | 44.44 | 2018 || 1003 | 66.66 | 2018 |+----------+--------------+--------+3 rows in set (0.03 sec)123456789101112
看一下上面的資料，第一條88.88的年份是2017年，我們再來看一下原始資料：
mysql> select * from t_order;+----+---------+---------------+-------+----------+| id | user_id | user_name | price | the_year |+----+---------+---------------+-------+----------+| 1 | 1001 | 路人甲Java | 11.11 | 2017 || 2 | 1001 | 路人甲Java | 22.22 | 2018 || 3 | 1001 | 路人甲Java | 88.88 | 2018 || 4 | 1002 | 劉德華 | 33.33 | 2018 || 5 | 1002 | 劉德華 | 12.22 | 2018 || 6 | 1002 | 劉德華 | 16.66 | 2018 || 7 | 1002 | 劉德華 | 44.44 | 2019 || 8 | 1003 | 張學友 | 55.55 | 2018 || 9 | 1003 | 張學友 | 66.66 | 2019 |+----+---------+---------------+-------+----------+9 rows in set (0.00 sec)123456789101112131415
對比一下，user_id=1001、price=88.88是第3條資料，即the_year是2018年，但是上面的分組結果是2017年，結果和我們預期的不一致，此時mysql對這種未按照規範來的列，亂序了，mysql取的是第一條。
正確的寫法，提供兩種，如下：
mysql> SELECT
user_id 使用者id,
price 最大金額,
the_year 年份 FROM
t_order t1 WHERE
(t1.user_id , t1.price)
IN
(SELECT
t.user_id, MAX(t.price)
FROM
t_order t GROUP BY t.user_id);+----------+--------------+--------+| 使用者id | 最大金額 | 年份 |+----------+--------------+--------+| 1001 | 88.88 | 2018 || 1002 | 44.44 | 2019 || 1003 | 66.66 | 2019 |+----------+--------------+--------+3 rows in set (0.00 sec)mysql> SELECT
user_id 使用者id,
price 最大金額,
the_year 年份 FROM
t_order t1,(SELECT
t.user_id uid, MAX(t.price) pc FROM
t_order t GROUP BY t.user_id) t2 WHERE
t1.user_id = t2.uid AND t1.price = t2.pc;+----------+--------------+--------+| 使用者id | 最大金額 | 年份 |+----------+--------------+--------+| 1001 | 88.88 | 2018 || 1002 | 44.44 | 2019 || 1003 | 66.66 | 2019 |+----------+--------------+--------+3 rows in set (0.00 sec)1234567891011121314151617181920212223242526272829303132333435363738394041424344
上面第1種寫法，比較少見，in中使用了多欄位查詢。
建議：在寫分組查詢的時候，最好按照標準的規範來寫，select後面出現的列必須在group by中或者必須使用聚合函式。
總結
-
在寫分組查詢的時候，最好按照標準的規範來寫，select後面出現的列必須在group by中或者必須使用聚合函式。
-
select語法順序：select、from、where、group by、having、order by、limit，順序不能搞錯了，否則報錯。
-
in多列查詢的使用，下去可以試試
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30239065/viewspace-2725385/，如需轉載，請註明出處，否則將追究法律責任。