select 之分组查询_select分组查询

分组查询

select 查询列表
from 表
【where 筛选条件】
group by 分组的字段
【order by 排序的字段】;
1
2
3
4
5

分组查询 group by

分组筛选 Having

特点

• 1: 和分组函数一同查询的字段必须是 group by 后出现的字段
• 筛选 having 分为两类分组前和分组后筛选

	1 ：分组函数做条件的肯定是放在having子句中
	2： 能用分组函数前筛选的就优先考虑使用分组前筛选
1
2

• 分组可以按多个字段也可以按多个字段，多个字段之间用逗号隔开没有顺序要求
• 可以搭配排序使用（排序放在整个分组查询的最后）

分组的筛选是难点

我们要看筛选的条件是不是  原始表所具备的如果不是原始表所有的字段，那么就放在分组后的子句后使用关键字 having， 如果是原始表内的字段就放在from表后使用where关键字，分组函数做筛选条件肯定是放在having子句后
1

分组查询的分析

1 ： 要仔细分析是对哪个字段 哪个条件来分组，然后分析哪个条件是用来筛选的 再分析筛选的条件是否是原始表内所具有的
order by having 要先把筛选条件剔除 一步步来做 最后再将 筛选条件放在having后

• eg:
  基础分组查询

-- 查找邮箱中包含a字符的，每个部门的平均薪资
select avg(salary) 平均薪资, department_id from employees where email like "%a%" group by department_id;


-- 查询有奖金的每个领导的手下员工的最高薪资
    --  解答： 我们可以知道每个领导  那就是按照领导来分组 
select  MAX(salary) 最高薪资, manager_id from employees group by manager_id;

1
2
3
4
5
6
7
8

复杂分组

# 案例1 ： 查询哪个部门的员工个数大于2
-- 分为两步解答
	-- a 先查询每个部门的员工个数
	select count(*),department_id from employees GROUP BY department_id;
	
	-- b 根据a的结果进行筛选，查询哪个部门的员工个数大于2,

	--  下面是错误的写法 ，因为count(*) 是没有在表中的字段 所以会报错 我们可以在查询完分组后的语句后使用count(*)>2因为这个时候第一步查完的语句是由count(*) 这个时候就用到了分组后筛选 having
	select COUNT(*) 个数, department_id from employees where COUNT(*) > 2 group by department_id;

-- 正确写法
select COUNT(*) 个数, department_id from employees GROUP BY department_id HAVING COUNT(*) > 2;


# 案例2 查询每个工种有奖金的员工的最高工资

select MAX(salary) 最高薪资, job_id from employees  where commission_pct is not null group by job_id;

# 案例3 查询每个工种有奖金的员工的最高工资> 12000的工种编号和最高薪资

--  a 查询每个工种有奖金的员工的最高薪资
    select MAX(salary), job_id from employees group by job_id;

--  下面查询是否有奖金，奖金字段在原表中是有的  所以放在 from子句后面使用where关键字 

--  b根据a的加过继续筛选 最高薪资> 12000 因为最高薪资在原始表中没有 所以放在分组后的表后 使用having关键字
select MAX(salary) 最高薪资, job_id from employees  WHERE commission_pct is not null group by job_id HAVING MAX(salary) > 12000; 


# 查询领导编号>102的每个领导手下的最低工资> 5000的领导编号是哪个,以及其最低薪资
    -- a查询每个领导手下员工的最低薪资
		select MIN(salary), manager_id from employees group by manager_id;
		
		-- b ： 筛选条件 编号 > 102  因为领导编号在原始表中存在，所以放在from子句后 使用where关键字
		select MIN(salary) 最低薪资, manager_id from employees where manager_id > 102 GROUP BY manager_id;
		
		-- c . 添加筛选条件	最低薪资 > 5000
		SELECT MIN(salary) 最低薪资, manager_id from employees where manager_id > 102 GROUP BY manager_id HAVING MIN(salary) > 5000;

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
27
28
29
30
31
32
33
34
35
36
37
38
39

按多个字段分组

• 多个分组条件就在中间加上,逗号 隔开
• group by 条件1，条件2

-- 查询每个部门每个工种的工作的平均薪资
select avg(salary),department_id,job_id from employees group by department_id, job_id;

# 添加排序
-- 查询每个部门每个工种的平均薪资，按平均薪资的高低排序

select avg(salary),department_id,job_id from employees group by  department_id,job_id order by avg(salary) desc;

1
2
3
4
5
6
7
8

** 由上可以看出order by 一般都是放在除了limit的最后所以排序放在分组后 **

Practices

#1.查询各job_id的员工工资的最大值，最小值，平均值，总和，并按job_id升序

select MAX(salary) 最高薪资, MIN(salary) 最低薪资, AVG(salary) 平均薪资, SUM(salary) 总薪资 from employees GROUP BY job_id order by job_id


#2.查询员工最高工资和最低工资的差距（DIFFERENCE）

select MAX(salary) 最高薪资, MIN(salary) 最低薪资, (MAX(salary) - MIN(salary)) DIFFERENCE from employees 


-- select MAX(salary) as gao, MIN(salary) as di, ( 'gao' - 'di') DIFFERENCE from employees; 这里是不可以的 因为 gao  和di 字段在原始表是不存在的 是无法使用在from前的  如果使用在from后是可以的 因为from后就是


# 3.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内

select MIN(salary) 最低薪资, job_id from employees  where manager_id is not null  GROUP BY job_id  HAVING 最低薪资 >= 6000;

select MIN(salary) 最低薪资, job_id FROM employees where manager_id is not null  GROUP BY job_id HAVING MIN(salary) >= 6000;

#4.查询所有部门的编号，员工数量和工资平均值,并按平均工资降序
select COUNT(*) 员工数量, AVG(salary) 平均薪资, department_id 部门编号 from employees GROUP BY department_id ORDER BY 平均薪资 desc  

#5.选择具有各个job_id的员工人数

select COUNT(*) 人数,job_id FROM employees GROUP BY job_id;

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