Mysql系列第九講 分組查詢詳解（group by & having）

分組查詢

語法：

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後的列

• 或者使用聚合函式的列

聚合函式

分組時，可以使用使用上面的聚合函式。

準備資料

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種：

1. 出現在group by後面的列

2. 使用聚合函式的列

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中或者必須使用聚合函式。

總結

1. 在寫分組查詢的時候，最好按照標準的規範來寫，select後面出現的列必須在group by中或者必須使用聚合函式。

2. select語法順序：select、from、where、group by、having、order by、limit，順序不能搞錯了，否則報錯。

3. in多列查詢的使用，下去可以試試