分组查询(group by、having)
目录
分组查询
语法：
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 | 求和，返回指定列的总和 |
| agv | 求平均值，返回指定列数据的平均值 |
准备测试数据
mysql> drop table if exists t_order;
mysql> 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 '订单表';
mysql> insert into t_order(user_id,user_name,price,the_year) values (1001,'周星驰',11.11,'2017'), (1001,'周星驰',22.22,'2018'), (1001,'周星驰',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 | 周星驰 | 11.11 | 2017 |
| 2 | 1001 | 周星驰 | 22.22 | 2018 |
| 3 | 1001 | 周星驰 | 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、下单数量，如下：
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.01 sec)
分组后对数据筛选，使用having关键字
需求：查询2018年订单数量大于1的用户，输出：用户id，下单数量，如下：
分组后筛选数据(having)
分组后对数据筛选，使用having关键字
作用：having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件显示特定的组，也可以使用多个分组标准进行分组。
语法：select 列名 , 列函数 from 表名 where 条件 group by 列名 having 子句;
需求：查询2018年订单数量大于1的用户，输出：用户id，下单数量，如下：
方式1：
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：
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 group by user_id;
+----------+--------------+
| 用户id | 最大金额 |
+----------+--------------+
| 1001 | 88.88 |
| 1002 | 44.44 |
| 1003 | 66.66 |
+----------+--------------+
3 rows in set (0.00 sec)
mysql> select user_id 用户id, max(price) 最大金额 from t_order group by user_id order by max(price) 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，下单数量，如下：
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;
+----------+--------------+
| 用户id | 下单数量 |
+----------+--------------+
| 1002 | 3 |
| 1001 | 2 |
+----------+--------------+
2 rows in set (0.00 sec)
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)
in多列查询
需求：获取每个用户下单的最大金额及下单的年份，输出：用户id，最大金额，年份
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)
#分解一下
where之前
mysql> SELECT
-> user_id 用户id, price 最大金额, the_year 年份
-> FROM
-> t_order t1;
+----------+--------------+--------+
| 用户id | 最大金额 | 年份 |
+----------+--------------+--------+
| 1001 | 11.11 | 2017 |
| 1001 | 22.22 | 2018 |
| 1001 | 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 |
+----------+--------------+--------+
9 rows in set (0.00 sec)
in之后的
mysql> SELECT t.user_id, MAX(t.price) FROM t_order t GROUP BY t.user_id
-> ;
+---------+--------------+
| user_id | MAX(t.price) |
+---------+--------------+
| 1001 | 88.88 |
| 1002 | 44.44 |
| 1003 | 66.66 |
+---------+--------------+
3 rows in set (0.00 sec)
总结：
分组中select后面的列只能有2种：
-
出现在group by后面的列
-
使用聚合函数的列
oracle、sqlserver、db2中也是按照这种规范来的。
文中使用的是5.7版本，默认是按照这种规范来的。
