语法:
select 分组函数(), 列(要求出现在 group by 后面的字段)(5)
from 表 (1)
[where 筛选条件] (2)
group by 分组列表 (3)
[having 分组后的筛选] (4)
[order by 排序字段] (6)
#(1),(2),(3),(4),(5),(6)为执行顺序.
注意:
1. 查询列表必须要是在分组函数和 group by 后出现的字段.
2. 分组查询中的筛选条件分为两种(能用分组前筛选的, 就优先考虑使用分组前筛选.
数据源 位置 关键字
分组前筛选 原始表 group by 子句前面 where
分组后的筛选 分组后的结果集 group by 子句后面 having
3.group by 子句支持单个字段分组, 多个字段分组(多个字段用逗号隔开, 没有顺序要求), 表达式, 函数, 别名(当使用别名时, 要全部使用别名), 也可以添加排序.
一. 简单分组查询练习
mysql> #1. 查询每个部门的评价工资
mysql> select avg(salary) 平均工资, department_id from employees group by department_id;
+--------------+---------------+
| 平均工资 | department_id |
- +--------------+---------------+
- | 7000.000000 | NULL |
- | 4400.000000 | 10 |
- | 9500.000000 | 20 |
- | 4150.000000 | 30 |
- | 6500.000000 | 40 |
- | 3475.555556 | 50 |
- | 5760.000000 | 60 |
- | 10000.000000 | 70 |
- | 8955.882353 | 80 |
- | 19333.333333 | 90 |
- | 8600.000000 | 100 |
- | 10150.000000 | 110 |
- +--------------+---------------+
- 12 rows in set (0.02 sec)
- #2. 查询每个位置上的部门个数.
mysql> select count(*) 部门个数, location_id from departments group by location_id;
+--------------+-------------+
| 部门个数 | location_id |
- +--------------+-------------+
- | 1 | 1400 |
- | 1 | 1500 |
- | 21 | 1700 |
- | 1 | 1800 |
- | 1 | 2400 |
- | 1 | 2500 |
- | 1 | 2700 |
- +--------------+-------------+
- 7 rows in set (0.07 sec)
- mysql> #3. 查询每个工种的最高工资
mysql> select max(salary) 最高工资, job_id from employees group by job_id;
+--------------+------------+
| 最高工资 | job_id |
- +--------------+------------+
- | 8300.00 | AC_ACCOUNT |
- | 12000.00 | AC_MGR |
- | 4400.00 | AD_ASST |
- | 24000.00 | AD_PRES |
- | 17000.00 | AD_VP |
- | 9000.00 | FI_ACCOUNT |
- | 12000.00 | FI_MGR |
- | 6500.00 | HR_REP |
- | 9000.00 | IT_PROG |
- | 13000.00 | MK_MAN |
- | 6000.00 | MK_REP |
- | 10000.00 | PR_REP |
- | 3100.00 | PU_CLERK |
- | 11000.00 | PU_MAN |
- | 14000.00 | SA_MAN |
- | 11500.00 | SA_REP |
- | 4200.00 | SH_CLERK |
- | 3600.00 | ST_CLERK |
- | 8200.00 | ST_MAN |
- +--------------+------------+
- 19 rows in set (0.00 sec)
二. 添加筛选条件分组
mysql> #1. 查询邮箱包含 e 字符的, 每个部门的平均工资
mysql> select avg(salary) 平均工资, department_id from employees where email like '%e%' group by department_id;
+--------------+---------------+
| 平均工资 | department_id |
- +--------------+---------------+
- | 4400.000000 | 10 |
- | 13000.000000 | 20 |
- | 6750.000000 | 30 |
- | 3221.052632 | 50 |
- | 5100.000000 | 60 |
- | 10000.000000 | 70 |
- | 9781.250000 | 80 |
- | 17000.000000 | 90 |
- | 9733.333333 | 100 |
- | 8300.000000 | 110 |
- +--------------+---------------+
- 10 rows in set (0.09 sec)
注意当我又想看邮箱是否含有'e'时, 我把字段'email'也放在查询列表里, 然后就报错了因为查询列表必须只能是在分组函数和 group by 后出现的字段.
mysql> #2. 查询邮箱包含 e 字符的, 每个部门的平均工资
mysql> select avg(salary) 平均工资, department_id from employees where email like '%e%' group by department_id;
+--------------+---------------+
| 平均工资 | department_id |
- +--------------+---------------+
- | 4400.000000 | 10 |
- | 13000.000000 | 20 |
- | 6750.000000 | 30 |
- | 3221.052632 | 50 |
- | 5100.000000 | 60 |
- | 10000.000000 | 70 |
- | 9781.250000 | 80 |
- | 17000.000000 | 90 |
- | 9733.333333 | 100 |
- | 8300.000000 | 110 |
- +--------------+---------------+
- 10 rows in set (0.09 sec)
mysql> select avg(salary) 平均工资, department_id,email from employees where email like '%e%' group by department_id;
- ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'myemployees.employees.email' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
- mysql> #3. 查询有奖金的每个领导手下员工的最高工资
mysql> select max(salary) 最高工资, manager_id from employees where commission_pct is not null group by manager_id;
+--------------+------------+
| 最高工资 | manager_id |
- +--------------+------------+
- | 14000.00 | 100 |
- | 10000.00 | 145 |
- | 10000.00 | 146 |
- | 10500.00 | 147 |
- | 11500.00 | 148 |
- | 11000.00 | 149 |
- +--------------+------------+
- 6 rows in set (0.29 sec)
三. 按表达式 / 函数 / 别名分组查询
- mysql> #1. 查询员工个数大于 2 的部门
- mysql> #(1)首先查询出每个部门的员工个数
mysql> select count(*) 员工个数, department_id from employees group by department_id;
+--------------+---------------+
| 员工个数 | department_id |
- +--------------+---------------+
- | 1 | NULL |
- | 1 | 10 |
- | 2 | 20 |
- | 6 | 30 |
- | 1 | 40 |
- | 45 | 50 |
- | 5 | 60 |
- | 1 | 70 |
- | 34 | 80 |
- | 3 | 90 |
- | 6 | 100 |
- | 2 | 110 |
- +--------------+---------------+
- 12 rows in set (0.00 sec)
- mysql> #根据 (1) 的结果进行筛选
mysql> select count(*) 员工个数, department_id from employees group by department_id having 员工个数 > 2;
+--------------+---------------+
| 员工个数 | department_id |
- +--------------+---------------+
- | 6 | 30 |
- | 45 | 50 |
- | 5 | 60 |
- | 34 | 80 |
- | 3 | 90 |
- | 6 | 100 |
- +--------------+---------------+
- 6 rows in set (0.00 sec)
- mysql> #2. 查询每个工种有奖金的员工的最高工资> 12000 的工种号和最高工资
mysql> select job_id , max(salary) 最高工资 from employees where commission_pct is not null group by job_id having 最高工资 > 12000;
+--------+--------------+
| job_id | 最高工资 |
- +--------+--------------+
- | SA_MAN | 14000.00 |
- +--------+--------------+
- 1 row in set (0.00 sec)
- mysql> #3. 查询领导编号 > 102 的每个领导手下的最低工资 > 5000 的领导编号是哪个, 以及其 最低工资
- mysql> select min(salary),manager_id from employees where manager_id>102 group by manager_id having min(salary)>5000;
- +-------------+------------+
- | min(salary) | manager_id |
- +-------------+------------+
- | 6900.00 | 108 |
- | 7000.00 | 145 |
- | 7000.00 | 146 |
- | 6200.00 | 147 |
- | 6100.00 | 148 |
- | 6200.00 | 149 |
- | 6000.00 | 201 |
- | 8300.00 | 205 |
- +-------------+------------+
- 8 rows in set (0.00 sec)
- mysql> #4. 按员工姓名的长度分组, 查询每一组的员工的个数, 筛选员工个数 > 5 的有哪些
- mysql> select count(*) c,length(last_name) l from employees group by l having c>5;
- +----+------+
- | c | l |
- +----+------+
- | 29 | 5 |
- | 15 | 7 |
- | 28 | 6 |
- | 8 | 9 |
- | 11 | 4 |
- | 7 | 8 |
- +----+------+
- 6 rows in set (0.06 sec)
. 四 按多个字段分组查询
- mysql> #查询每个部门每个工种的员工的平均工资
- mysql> select avg(salary), department_id ,job_id from employees group by department_id,job_id;
- +--------------+---------------+------------+
- | avg(salary) | department_id | job_id |
- +--------------+---------------+------------+
- | 24000.000000 | 90 | AD_PRES |
- | 17000.000000 | 90 | AD_VP |
- | 5760.000000 | 60 | IT_PROG |
- | 12000.000000 | 100 | FI_MGR |
- | 7920.000000 | 100 | FI_ACCOUNT |
- | 11000.000000 | 30 | PU_MAN |
- | 2780.000000 | 30 | PU_CLERK |
- | 7280.000000 | 50 | ST_MAN |
- | 2785.000000 | 50 | ST_CLERK |
- | 12200.000000 | 80 | SA_MAN |
- | 8396.551724 | 80 | SA_REP |
- | 7000.000000 | NULL | SA_REP |
- | 3215.000000 | 50 | SH_CLERK |
- | 4400.000000 | 10 | AD_ASST |
- | 13000.000000 | 20 | MK_MAN |
- | 6000.000000 | 20 | MK_REP |
- | 6500.000000 | 40 | HR_REP |
- | 10000.000000 | 70 | PR_REP |
- | 12000.000000 | 110 | AC_MGR |
- | 8300.000000 | 110 | AC_ACCOUNT |
- +--------------+---------------+------------+
- 20 rows in set (0.00 sec)
. 五 添加排序
- mysql> #1. 查询每个部门每个工种的员工的平均工资 > 10000, 并按工资的高低排序
- mysql> select avg(salary), department_id ,job_id from employees group by department_id,job_id having avg(salary)>10000 order by avg(salary) desc;
- +--------------+---------------+---------+
- | avg(salary) | department_id | job_id |
- +--------------+---------------+---------+
- | 24000.000000 | 90 | AD_PRES |
- | 17000.000000 | 90 | AD_VP |
- | 13000.000000 | 20 | MK_MAN |
- | 12200.000000 | 80 | SA_MAN |
- | 12000.000000 | 110 | AC_MGR |
- | 12000.000000 | 100 | FI_MGR |
- | 11000.000000 | 30 | PU_MAN |
- +--------------+---------------+---------+
- 7 rows in set (0.00 sec)
注: 这是本人的学习笔记及练习, 如果有错误的地方望指出一起讨论, 谢谢!
来源: http://www.jianshu.com/p/5879ef4653ea