玩转Mysql系列 - 第9篇:分组查询详解(group by & having)

打算提升sql技能的,可以加我微信itsoku,带你成为sql高手。

这是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多列查询的使用

分组查询

语法:

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

说明:

group_function:聚合函数。

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

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

分组中,select后面只能有两种类型的列:

  1. 出现在group by后的列

  2. 或者使用聚合函数的列

聚合函数

函数名称作用
max查询指定列的最大值
min查询指定列的最小值
count统计查询结果的行数
sum求和,返回指定列的总和
avg求平均值,返回指定列数据的平均值

分组时,可以使用使用上面的聚合函数。

准备数据

  1. drop table if exists t_order;
  2. -- 创建订单表
  3. create table t_order(
  4.   id int not null AUTO_INCREMENT COMMENT '订单id',
  5.   user_id bigint not null comment '下单人id',
  6.   user_name varchar(16) not null default '' comment '用户名',
  7.   price decimal(10,2) not null default 0 comment '订单金额',
  8.   the_year SMALLINT not null comment '订单创建年份',
  9.   PRIMARY KEY (id)
  10. ) comment '订单表';
  11. -- 插入数据
  12. insert into t_order(user_id,user_name,price,the_year) values
  13.   (1001,'路人甲Java',11.11,'2017'),
  14.   (1001,'路人甲Java',22.22,'2018'),
  15.   (1001,'路人甲Java',88.88,'2018'),
  16.   (1002,'刘德华',33.33,'2018'),
  17.   (1002,'刘德华',12.22,'2018'),
  18.   (1002,'刘德华',16.66,'2018'),
  19.   (1002,'刘德华',44.44,'2019'),
  20.   (1003,'张学友',55.55,'2018'),
  21.   (1003,'张学友',66.66,'2019');
  1. mysql> select * from t_order;
  2. +----+---------+---------------+-------+----------+
  3. | id | user_id | user_name     | price | the_year |
  4. +----+---------+---------------+-------+----------+
  5. |  1 |    1001 | 路人甲Java    | 11.11 |     2017 |
  6. |  2 |    1001 | 路人甲Java    | 22.22 |     2018 |
  7. |  3 |    1001 | 路人甲Java    | 88.88 |     2018 |
  8. |  4 |    1002 | 刘德华        | 33.33 |     2018 |
  9. |  5 |    1002 | 刘德华        | 12.22 |     2018 |
  10. |  6 |    1002 | 刘德华        | 16.66 |     2018 |
  11. |  7 |    1002 | 刘德华        | 44.44 |     2019 |
  12. |  8 |    1003 | 张学友        | 55.55 |     2018 |
  13. |  9 |    1003 | 张学友        | 66.66 |     2019 |
  14. +----+---------+---------------+-------+----------+
  15. 9 rows in set (0.00 sec)

单字段分组

需求:查询每个用户下单数量,输出:用户id、下单数量,如下:

  1. mysql> SELECT 
  2.             user_id 用户id, COUNT(id) 下单数量
  3.         FROM
  4.             t_order
  5.         GROUP BY user_id;
  6. +----------+--------------+
  7. | 用户id   | 下单数量     |
  8. +----------+--------------+
  9. |     1001 |            3 |
  10. |     1002 |            4 |
  11. |     1003 |            2 |
  12. +----------+--------------+
  13. 3 rows in set (0.00 sec)

多字段分组

需求:查询每个用户每年下单数量,输出字段:用户id、年份、下单数量,如下:

  1. mysql> SELECT 
  2.             user_id 用户id, the_year 年份, COUNT(id) 下单数量
  3.         FROM
  4.             t_order
  5.         GROUP BY user_id , the_year;
  6. +----------+--------+--------------+
  7. | 用户id   | 年份   | 下单数量     |
  8. +----------+--------+--------------+
  9. |     1001 |   2017 |            1 |
  10. |     1001 |   2018 |            2 |
  11. |     1002 |   2018 |            3 |
  12. |     1002 |   2019 |            1 |
  13. |     1003 |   2018 |            1 |
  14. |     1003 |   2019 |            1 |
  15. +----------+--------+--------------+
  16. 6 rows in set (0.00 sec)

分组前筛选数据

分组前对数据进行筛选,使用where关键字

需求:需要查询2018年每个用户下单数量,输出:用户id、下单数量,如下:

  1. mysql> SELECT 
  2.             user_id 用户id, COUNT(id) 下单数量
  3.         FROM
  4.             t_order t
  5.         WHERE
  6.             t.the_year = 2018
  7.         GROUP BY user_id;
  8. +----------+--------------+
  9. | 用户id   | 下单数量     |
  10. +----------+--------------+
  11. |     1001 |            2 |
  12. |     1002 |            3 |
  13. |     1003 |            1 |
  14. +----------+--------------+
  15. 3 rows in set (0.00 sec)

分组后筛选数据

分组后对数据筛选,使用having关键字

需求:查询2018年订单数量大于1的用户,输出:用户id,下单数量,如下:

方式1:

  1. mysql> SELECT
  2.           user_id 用户id, COUNT(id) 下单数量
  3.         FROM
  4.           t_order t
  5.         WHERE
  6.           t.the_year = 2018
  7.         GROUP BY user_id
  8.         HAVING count(id)>=2;
  9. +----------+--------------+
  10. | 用户id   | 下单数量     |
  11. +----------+--------------+
  12. |     1001 |            2 |
  13. |     1002 |            3 |
  14. +----------+--------------+
  15. 2 rows in set (0.00 sec)

方式2:

  1. mysql> SELECT
  2.           user_id 用户id, count(id) 下单数量
  3.         FROM
  4.           t_order t
  5.         WHERE
  6.           t.the_year = 2018
  7.         GROUP BY user_id
  8.         HAVING 下单数量>=2;
  9. +----------+--------------+
  10. | 用户id   | 下单数量     |
  11. +----------+--------------+
  12. |     1001 |            2 |
  13. |     1002 |            3 |
  14. +----------+--------------+
  15. 2 rows in set (0.00 sec)

where和having的区别

where是在分组(聚合)前对记录进行筛选,而having是在分组结束后的结果里筛选,最后返回整个sql的查询结果。

可以把having理解为两级查询,即含having的查询操作先获得不含having子句时的sql查询结果表,然后在这个结果表上使用having条件筛选出符合的记录,最后返回这些记录,因此,having后是可以跟聚合函数的,并且这个聚集函数不必与select后面的聚集函数相同。

分组后排序

需求:获取每个用户最大金额,然后按照最大金额倒序,输出:用户id,最大金额,如下:

  1. mysql> SELECT
  2.           user_id 用户id, max(price) 最大金额
  3.         FROM
  4.           t_order t
  5.         GROUP BY user_id
  6.         ORDER BY 最大金额 desc;
  7. +----------+--------------+
  8. | 用户id   | 最大金额     |
  9. +----------+--------------+
  10. |     1001 |        88.88 |
  11. |     1003 |        66.66 |
  12. |     1002 |        44.44 |
  13. +----------+--------------+
  14. 3 rows in set (0.00 sec)

where & group by & having & order by & limit 一起协作

where、group by、having、order by、limit这些关键字一起使用时,先后顺序有明确的限制,语法如下:

  1. select 列 from 
  2. 表名
  3. where [查询条件]
  4. group by [分组表达式]
  5. having [分组过滤条件]
  6. order by [排序条件]
  7. limit [offset,] count;

注意:

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

示例:

需求:查询出2018年,下单数量大于等于2的,按照下单数量降序排序,最后只输出第1条记录,显示:用户id,下单数量,如下:

  1. mysql> SELECT
  2.           user_id 用户id, COUNT(id) 下单数量
  3.         FROM
  4.           t_order t
  5.         WHERE
  6.           t.the_year = 2018
  7.         GROUP BY user_id
  8.         HAVING count(id)>=2
  9.         ORDER BY 下单数量 DESC
  10.         LIMIT 1;
  11. +----------+--------------+
  12. | 用户id   | 下单数量     |
  13. +----------+--------------+
  14. |     1002 |            3 |
  15. +----------+--------------+
  16. 1 row in set (0.00 sec)

mysql分组中的坑

本文开头有介绍,分组中select后面的列只能有2种:

  1. 出现在group by后面的列

  2. 使用聚合函数的列

oracle、sqlserver、db2中也是按照这种规范来的。

文中使用的是5.7版本,默认是按照这种规范来的。

mysql早期的一些版本,没有上面这些要求,select后面可以跟任何合法的列。

示例

需求:获取每个用户下单的最大金额及下单的年份,输出:用户id,最大金额,年份,写法如下:

  1. mysql> select
  2.           user_id 用户id, max(price) 最大金额, the_year 年份
  3.         FROM t_order t
  4.         GROUP BY t.user_id;
  5. 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的配置:

  1. mysql> select @@sql_mode;
  2. +-------------------------------------------------------------------------------------------------------------------------------------------+
  3. | @@sql_mode                                                                                                                                |
  4. +-------------------------------------------------------------------------------------------------------------------------------------------+
  5. 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 |
  6. +-------------------------------------------------------------------------------------------------------------------------------------------+
  7. 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,再次运行,效果如下:

  1. mysql> select
  2.           user_id 用户id, max(price) 最大金额, the_year 年份
  3.         FROM t_order t
  4.         GROUP BY t.user_id;
  5. +----------+--------------+--------+
  6. | 用户id   | 最大金额     | 年份   |
  7. +----------+--------------+--------+
  8. |     1001 |        88.88 |   2017 |
  9. |     1002 |        44.44 |   2018 |
  10. |     1003 |        66.66 |   2018 |
  11. +----------+--------------+--------+
  12. 3 rows in set (0.03 sec)

看一下上面的数据,第一条88.88的年份是2017年,我们再来看一下原始数据:

  1. mysql> select * from t_order;
  2. +----+---------+---------------+-------+----------+
  3. | id | user_id | user_name     | price | the_year |
  4. +----+---------+---------------+-------+----------+
  5. |  1 |    1001 | 路人甲Java    | 11.11 |     2017 |
  6. |  2 |    1001 | 路人甲Java    | 22.22 |     2018 |
  7. |  3 |    1001 | 路人甲Java    | 88.88 |     2018 |
  8. |  4 |    1002 | 刘德华        | 33.33 |     2018 |
  9. |  5 |    1002 | 刘德华        | 12.22 |     2018 |
  10. |  6 |    1002 | 刘德华        | 16.66 |     2018 |
  11. |  7 |    1002 | 刘德华        | 44.44 |     2019 |
  12. |  8 |    1003 | 张学友        | 55.55 |     2018 |
  13. |  9 |    1003 | 张学友        | 66.66 |     2019 |
  14. +----+---------+---------------+-------+----------+
  15. 9 rows in set (0.00 sec)

对比一下,user_id=1001、price=88.88是第3条数据,即the_year是2018年,但是上面的分组结果是2017年,结果和我们预期的不一致,此时mysql对这种未按照规范来的列,乱序了,mysql取的是第一条。

正确的写法,提供两种,如下:

  1. mysql> SELECT
  2.           user_id 用户id,
  3.           price 最大金额,
  4.           the_year 年份
  5.         FROM
  6.           t_order t1
  7.         WHERE
  8.           (t1.user_id , t1.price)
  9.           IN
  10.           (SELECT
  11.              t.user_idMAX(t.price)
  12.            FROM
  13.              t_order t
  14.            GROUP BY t.user_id);
  15. +----------+--------------+--------+
  16. | 用户id   | 最大金额     | 年份   |
  17. +----------+--------------+--------+
  18. |     1001 |        88.88 |   2018 |
  19. |     1002 |        44.44 |   2019 |
  20. |     1003 |        66.66 |   2019 |
  21. +----------+--------------+--------+
  22. 3 rows in set (0.00 sec)
  23. mysql> SELECT
  24.           user_id 用户id,
  25.           price 最大金额,
  26.           the_year 年份
  27.         FROM
  28.           t_order t1,(SELECT
  29.                         t.user_id uid, MAX(t.price) pc
  30.                       FROM
  31.                         t_order t
  32.                       GROUP BY t.user_id) t2
  33.         WHERE
  34.           t1.user_id = t2.uid
  35.         AND  t1.price = t2.pc;
  36. +----------+--------------+--------+
  37. | 用户id   | 最大金额     | 年份   |
  38. +----------+--------------+--------+
  39. |     1001 |        88.88 |   2018 |
  40. |     1002 |        44.44 |   2019 |
  41. |     1003 |        66.66 |   2019 |
  42. +----------+--------------+--------+
  43. 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提供的一些好用的并发工具类

 

 

 

来源:https://itsoku.blog.csdn.net/article/details/100986411
posted @ 2022-04-23 21:56  程序员小明1024  阅读(247)  评论(0编辑  收藏  举报