- 语法
-
SELECT column, group_function,... FROM table [WHERE condition] GROUP BY group_by_expression [HAVING group_condition];
- group_function：聚合函数
- group_by_expression：分组表达式，多个之间用逗号隔开
- group_condition：分组之后对数据进⾏过滤
- 分组中，select后面只能有两种类型的列
- 出现在group by后的列
- 或者使用聚合函数的列
- 聚合函数
- max 查询指定列的最大值
- min 查询指定列的最小值
- count 统计查询结果的行数
- sum 返回指定列的总和
- avg 返回指定列数据的平均值
/*
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)
*/
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)
*/
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)
*/
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)
*/
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)
*/
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后面的聚集函数相同。
分组后排序
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 一起协作
-
select 列 from 表名 where [查询条件]
group by [分组表达式] having [分组过滤条件]
order by [排序条件] limit [offset,] count;
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分组中的坑
-- 错误示例
-- 因为 the_year 不符合上面说的2条规则（select后面的列必须出现在group by中或者使用聚合函数）
select
user_id 用户id, max(price) 最大金额, the_year 年份
FROM t_order t
GROUP BY t.user_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
);
-- or
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;
总结
- 在写分组查询的时候，最好按照标准的规范来写，select后面出现的列必须在group by中或者必须使用聚合函数
- select语法顺序：select、from、where、group by、having、order by、limit，顺序不能搞错了，否则报错
- in多列查询的使用
网友评论