聚合函数
# 聚合函数
作用于一组函数，只返回一个值。
# 常见的聚合函数
# AVG、SUM
只适合数值类型的字段
SELECT
AVG(salary),SUM(salary),SUM(last_name)
FROM employees;
1
2
3
2
3
+-------------+-------------+----------------+
| AVG(salary) | SUM(salary) | SUM(last_name) |
+-------------+-------------+----------------+
| 6461.682243 | 691400 | 0 |
+-------------+-------------+----------------+
1 row in set, 107 warnings (0.0012 sec)
Warning (code 1292): Truncated incorrect DOUBLE value: 'King'
Warning (code 1292): Truncated incorrect DOUBLE value: 'Kochhar'
Warning (code 1292): Truncated incorrect DOUBLE value: 'De Haan'
-- ... ...
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
# MAX、MIN
可以任何数据类型的字段。
SELECT
MAX(salary),MIN(salary),MIN(last_name)
FROM employees;
1
2
3
2
3
+-------------+-------------+----------------+
| MAX(salary) | MIN(salary) | MIN(last_name) |
+-------------+-------------+----------------+
| 24000 | 2100 | Abel |
+-------------+-------------+----------------+
1 row in set (0.0007 sec)
1
2
3
4
5
6
2
3
4
5
6
# COUNT
计算记录总数，支持任何数据类型，会忽略 NULL。
SELECT
COUNT(*),COUNT(1),COUNT(department_id)
FROM employees;
1
2
3
2
3
+----------+----------+----------------------+
| COUNT(*) | COUNT(1) | COUNT(department_id) |
+----------+----------+----------------------+
| 107 | 107 | 106 |
+----------+----------+----------------------+
1 row in set (0.0012 sec)
1
2
3
4
5
6
2
3
4
5
6
AVG = SUM / COUNT
# GROUP BY
结合聚合函数一起使用，对结果集进行分组，声明在 FROM 之后，ORDER BY、LIMIT 之前，SELECT 中非聚合函数的字段必须出现在 GROUP BY 中。
SELECT department_id,AVG(salary) avg_salary
FROM employees
GROUP BY department_id
ORDER BY avg_salary ASC
LIMIT 5;
1
2
3
4
5
2
3
4
5
+---------------+-------------+
| department_id | avg_salary |
+---------------+-------------+
| 50 | 3475.555556 |
| 30 | 4150 |
| 10 | 4400 |
| 60 | 5760 |
| 40 | 6500 |
+---------------+-------------+
5 rows in set (0.0010 sec)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
# WITH ROLLUP
WITH ROLLUP 是 GROUP BY 子句的拓展，在分组的基础上再做一个统计。
SELECT
department_id,
AVG(salary) avg_salary,
SUM(salary) sum_salary
FROM employees
GROUP BY department_id
WITH ROLLUP;
1
2
3
4
5
6
7
2
3
4
5
6
7
+---------------+--------------+------------+
| department_id | avg_salary | sum_salary |
+---------------+--------------+------------+
| NULL | 7000 | 7000 |
| 10 | 4400 | 4400 |
| 20 | 9500 | 19000 |
| 30 | 4150 | 24900 |
| 40 | 6500 | 6500 |
| 50 | 3475.555556 | 156400 |
| 60 | 5760 | 28800 |
| 70 | 10000 | 10000 |
| 80 | 8955.882353 | 304500 |
| 90 | 19333.333333 | 58000 |
| 100 | 8600 | 51600 |
| 110 | 10150 | 20300 |
| NULL | 6461.682243 | 691400 |
+---------------+--------------+------------+
13 rows in set (0.0010 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
5.7版本及之前 WITH ROLLUP 与 ORDER BY 一起使用会报错，8.0 版本不会。
5.7 版本
mysql> SELECT
-> department_id,
-> AVG(salary) avg_salary,
-> SUM(salary) sum_salary
-> FROM employees
-> GROUP BY department_id
-> WITH ROLLUP
-> ORDER BY avg_salary;
ERROR 1221 (HY000): Incorrect usage of CUBE/ROLLUP and ORDER BY
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
8.0 版本
SQL > SELECT
-> department_id,
-> AVG(salary) avg_salary,
-> SUM(salary) sum_salary
-> FROM employees
-> GROUP BY department_id
-> WITH ROLLUP
-> ORDER BY avg_salary;
+---------------+--------------+------------+
| department_id | avg_salary | sum_salary |
+---------------+--------------+------------+
| 50 | 3475.555556 | 156400 |
| 30 | 4150 | 24900 |
| 10 | 4400 | 4400 |
| 60 | 5760 | 28800 |
| NULL | 6461.682243 | 691400 |
| 40 | 6500 | 6500 |
| NULL | 7000 | 7000 |
| 100 | 8600 | 51600 |
| 80 | 8955.882353 | 304500 |
| 20 | 9500 | 19000 |
| 70 | 10000 | 10000 |
| 110 | 10150 | 20300 |
| 90 | 19333.333333 | 58000 |
+---------------+--------------+------------+
13 rows in set (0.0012 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
# HAVING
用于过滤分组的子句，必须在 GROUP BY 后面，开发中使用 HAVING 的前提是使用了 GROUP BY，否则没有意义。过滤条件中有聚合函数使用 HAVING 子句，没有聚合函数从优化角度使用 WHERE 子句。
部门中最高工资大于10000的部门
SELECT
department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000;
1
2
3
4
5
2
3
4
5
+---------------+-------------+
| department_id | MAX(salary) |
+---------------+-------------+
| 20 | 13000 |
| 30 | 11000 |
| 80 | 14000 |
| 90 | 24000 |
| 100 | 12000 |
| 110 | 12000 |
+---------------+-------------+
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
# 初学底层 SQL 执行
# 声明顺序
SELECT...DISTINCT...FROM...JOIN...ON...WHERE...GROUP BY...WITH ROLLUPHAVING...ORDER BY...LIMIT
# 执行顺序
FROM...JOIN...ON...WHERE...GROUP BY...WITH ROLLUPHAVINGSELECTDISTINCTORDER BYLIMIT
上次更新: 2023/07/30, 20:56:10