Mysql高手系列 - 第9篇：详解分组查询，mysql分组有大坑！

这是Mysql系列第9篇。

环境：mysql5.7.25，cmd命令中进行演示。

本篇内容

  1. 分组查询语法
  2. 聚合函数
  3. 单字段分组
  4. 多字段分组
  5. 分组前筛选数据
  6. 分组后筛选数据
  7. where和having的区别
  8. 分组后排序
  9. where & group by & having & order by & limit 一起协作
  10. mysql分组中的坑
  11. in多列查询的使用

分组查询

语法：

SELECT column, group_function,... FROM table
[WHERE condition]
GROUP BY group_by_expression
[HAVING group_condition];

说明：

group_function：聚合函数。

group_by_expression：分组表达式，多个之间用逗号隔开。

group_condition：分组之后对数据进行过滤。

分组中，select后面只能有两种类型的列：

  1. 出现在group by后的列
  2. 或者使用聚合函数的列

聚合函数

函数名称 作用
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)

单字段分组

需求：查询每个用户下单数量，输出：用户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)

多字段分组

需求：查询每个用户每年下单数量，输出字段：用户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)

分组前筛选数据

分组前对数据进行筛选，使用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)

分组后筛选数据

分组后对数据筛选，使用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)

方式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)

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)

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;

注意：

写法上面必须按照上面的顺序来写。

示例：

需求：查询出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)

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_by

上面的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)

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_SUBSTITUTION

重启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)

看一下上面的数据，第一条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)

对比一下，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)

上面第1种写法，比较少见，in中使用了多字段查询。

建议：在写分组查询的时候，最好按照标准的规范来写，select后面出现的列必须在group by中或者必须使用聚合函数。

总结

  1. 在写分组查询的时候，最好按照标准的规范来写，select后面出现的列必须在group by中或者必须使用聚合函数。
  2. select语法顺序：select、from、where、group by、having、order by、limit，顺序不能搞错了，否则报错。
  3. in多列查询的使用，下去可以试试

Mysql系列目录

  1. 第1篇：mysql基础知识
  2. 第2篇：详解mysql数据类型（重点）
  3. 第3篇：管理员必备技能(必须掌握)
  4. 第4篇：DDL常见操作
  5. 第5篇：DML操作汇总（insert,update,delete）
  6. 第6篇：select查询基础篇
  7. 第7篇：玩转select条件查询，避免采坑
  8. 第8篇：详解排序和分页(order by & limit)

mysql系列大概有20多篇，喜欢的请关注一下，欢迎大家加我微信itsoku或者留言交流mysql相关技术!

java高并发系列全集

  1. 第1天:必须知道的几个概念
  2. 第2天:并发级别
  3. 第3天:有关并行的两个重要定律
  4. 第4天:JMM相关的一些概念
  5. 第5天:深入理解进程和线程
  6. 第6天:线程的基本操作
  7. 第7天:volatile与Java内存模型
  8. 第8天:线程组
  9. 第9天：用户线程和守护线程
  10. 第10天:线程安全和synchronized关键字
  11. 第11天:线程中断的几种方式
  12. 第12天JUC:ReentrantLock重入锁
  13. 第13天:JUC中的Condition对象
  14. 第14天:JUC中的LockSupport工具类，必备技能
  15. 第15天：JUC中的Semaphore（信号量）
  16. 第16天：JUC中等待多线程完成的工具类CountDownLatch，必备技能
  17. 第17天：JUC中的循环栅栏CyclicBarrier的6种使用场景
  18. 第18天：JAVA线程池，这一篇就够了
  19. 第19天：JUC中的Executor框架详解1
  20. 第20天：JUC中的Executor框架详解2
  21. 第21天：java中的CAS，你需要知道的东西
  22. 第22天：JUC底层工具类Unsafe，高手必须要了解
  23. 第23天：JUC中原子类，一篇就够了
  24. 第24天：ThreadLocal、InheritableThreadLocal（通俗易懂）
  25. 第25天：掌握JUC中的阻塞队列
  26. 第26篇：学会使用JUC中常见的集合，常看看！
  27. 第27天：实战篇，接口性能提升几倍原来这么简单
  28. 第28天：实战篇，微服务日志的伤痛，一并帮你解决掉
  29. 第29天：高并发中常见的限流方式
  30. 第30天：JUC中工具类CompletableFuture，必备技能
  31. 第31天：获取线程执行结果，这6种方法你都知道？
  32. 第32天：高并发中计数器的实现方式有哪些？
  33. 第33篇：怎么演示公平锁和非公平锁?
  34. 第34篇：google提供的一些好用的并发工具类

posted @ 2019-09-16 16:37  路人甲Java  阅读(2305)  评论(6)  编辑  收藏  举报