聚合函数

作用于一组函数，只返回一个值。

常见的聚合函数

AVG、SUM

只适合数值类型的字段

SELECT
AVG(salary),SUM(salary),SUM(last_name)
FROM employees;
+-------------+-------------+----------------+
| 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'
-- ... ...

MAX、MIN

可以任何数据类型的字段。

SELECT
MAX(salary),MIN(salary),MIN(last_name)
FROM employees;
+-------------+-------------+----------------+
| MAX(salary) | MIN(salary) | MIN(last_name) |
+-------------+-------------+----------------+
|       24000 |        2100 | Abel           |
+-------------+-------------+----------------+
1 row in set (0.0007 sec)

COUNT

计算记录总数，支持任何数据类型，会忽略 NULL。

SELECT
COUNT(*),COUNT(1),COUNT(department_id)
FROM employees;
+----------+----------+----------------------+
| COUNT(*) | COUNT(1) | COUNT(department_id) |
+----------+----------+----------------------+
|      107 |      107 |                  106 |
+----------+----------+----------------------+
1 row in set (0.0012 sec)

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;
+---------------+-------------+
| department_id | avg_salary  |
+---------------+-------------+
|            50 | 3475.555556 |
|            30 |        4150 |
|            10 |        4400 |
|            60 |        5760 |
|            40 |        6500 |
+---------------+-------------+
5 rows in set (0.0010 sec)

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;
+---------------+--------------+------------+
| 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)

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

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)

HAVING

用于过滤分组的子句，必须在 GROUP BY 后面，开发中使用 HAVING 的前提是使用了 GROUP BY，否则没有意义。过滤条件中有聚合函数使用 HAVING 子句，没有聚合函数从优化角度使用 WHERE 子句。

部门中最高工资大于10000的部门

SELECT
department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000;
+---------------+-------------+
| department_id | MAX(salary) |
+---------------+-------------+
|            20 |       13000 |
|            30 |       11000 |
|            80 |       14000 |
|            90 |       24000 |
|           100 |       12000 |
|           110 |       12000 |
+---------------+-------------+

初学底层 SQL 执行

声明顺序

  1. SELECT...
  2. DISTINCT...
  3. FROM...
  4. JOIN...ON...
  5. WHERE...
  6. GROUP BY...
  7. WITH ROLLUP
  8. HAVING...
  9. ORDER BY...
  10. LIMIT

执行顺序

  1. FROM...
  2. JOIN...ON...
  3. WHERE...
  4. GROUP BY...
  5. WITH ROLLUP
  6. HAVING
  7. SELECT
  8. DISTINCT
  9. ORDER BY
  10. LIMIT
文章目录