新闻资讯
MySQL学习,详解分组查询(一)
分组查询
语法:
SELECT column, group_function,... FROM table
[WHERE condition]
GROUP BY group_by_expression
[HAVING group_condition];
说明:
group_function:聚合函数。
groupbyexpression:分组表达式,多个之间⽤逗号隔开。
group_condition:分组之后对数据进⾏过滤。
分组中,select后⾯只能有两种类型的列:
1. 出现在group by后的列
2. 或者使⽤聚合函数的列准备数据
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后⾯的聚集函数相
同。
回复列表