SQL-DQL(6)之分组查询

语法：

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)

注：这是本人的学习笔记及练习，如果有错误的地方望指出一起讨论，谢谢！

SQL-DQL(6)之分组查询

语法：

注意：

√一. 简单分组查询练习

√二. 添加筛选条件分组

√三. 按表达式/函数/别名分组查询

√. 四 按多个字段分组查询

√. 五 添加排序

推荐阅读更多精彩内容

√. 四按多个字段分组查询

√. 五添加排序