使用GROUP BY分组

GROUP BY 的基本使用

使用GROUP BY将表中的数据分为若干组。

1. 查询整个公司的平均工资。

# 查询整个公司的平均工资
SELECT AVG(salary)
FROM employees; -- 返回6461.682243
1
2
3

2. 查询各个部门的平均工资。

# 查询各个部门的平均工资
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id; -- 返回12行记录
1
2
3
4

3. 查询各个工种的平均工资。

# 查询各个工种的平均工资
SELECT job_id,AVG(salary)
FROM employees
GROUP BY job_id; -- 返回19行记录
1
2
3
4

GROUP BY 多列分组，用逗号隔开

GROUP BY 多列分组，用逗号隔开。

1. 查询各个部门中各个工种（或者各个工种中各个部门）的平均工资

# 查询各个部门中各个工种的平均工资
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id,job_id; -- 返回20行记录

# 查询各个工种中的各个部门的平均工资
SELECT job_id,department_id,AVG(salary)
FROM employees
GROUP BY job_id,department_id; -- 返回20行记录
1
2
3
4
5
6
7
8
9

使用GORUP BY时的注意点

1. SELECT中出现的非组函数必须声明在GROUP BY中，而GROUP BY中声明的字段可以不出现在SELECT中。

# SELECT中出现的非组函数必须出现在GROUP BY中
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id,job_id;

# GROUP BY中声明的字段可以不出现在SELECT中
SELECT AVG(salary)
FROM employees
GROUP BY department_id,job_id;

# 错误的示例
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id,job_id; -- MySQL虽然没有报错，但数据依然不正确。Orcal中会直接报错。
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2. GROUP BY的位置：SELECT...FROM...WHERE...GROUP BY...ORDER BY...LIMIT...

# 查询各个部门的平均工资，且按照平均工资从低到高排序
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal ASC;
1
2
3
4
5

GROUP BY中使用WITH ROLLUP

使用WITH ROLLUP关键字后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和。

# 查询整个公司的平均工资
SELECT AVG(salary)
FROM employees; -- 返回6461.682243

# 查询各个部门的平均工资
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id; -- 返回12行记录

# 查询各个部门的平均工资
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id
WITH ROLLUP; -- 返回13行记录，且最后一条记录的数值是6461.682243，即整个公司的平均工资
1
2
3
4
5
6
7
8
9
10
11
12
13
14

注意，当使用WITH ROLLUP时，不能同时使用ORDER BY进行结果排序。也就是说，WITH ROLLUP和ORDER BY是互相排斥的。