sql分组查询

#分组查询
/*
语法：
	select 分组函数,列(要求出现在group by的后面)
	from 表
	[where 筛选条件]
	group by 分组的列表
	[order by 子句]
注意：查询列表必须特殊，要求是分组函数和group by后出现的字段
特点：
	1.分组查询中的筛选条件分为两类
			数据源		位置			关键字
	分组前筛选      原始表		group by子句的前面	where
	分组后筛选	分组后的结果集	group by子句的后面	having
	小抄：
	1).一般来讲，分组函数做条件肯定是放在having子句中
	2).能用分组前筛选的，优先考虑使用分组前筛选，考虑性能问题
	2.group子句支持单个字段分组，多个字段分组(多个字段之间用逗号隔开，没有顺序要求)，表达式或函数(用的较少)
	3.也可以添加排序，顺序：select/from/where/group by/having/order by
	
*/
#语法顺序：select from where group by order by 
#明确where一定要放在from后面
SELECT department_id,AVG(salary) FROM employees GROUP BY department_id;

SELECT
`department_id`,ROUND(AVG(salary),2) 各部门薪资水平
FROM employees
GROUP BY department_id

#案例一：查询每个工种的最高工资
SELECT `job_id` AS 工种,MAX(salary) AS 最高薪资
FROM employees
GROUP BY job_id; 
#案例二：查询每个位置上的部门个数

SELECT * FROM departments;

SELECT `location_id`,(department_id) 部门个数 FROM `departments`
GROUP BY `location_id`;

SELECT `location_id`,COUNT(*) FROM `departments`
GROUP BY `location_id`;

#添加筛选条件
#案例三：查询邮箱中包含a字符的，每个部门的平均工资
SELECT department_id AS 部门,
ROUND(AVG(salary)) AS 薪资水平
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id
;
#注意：如果where放在group by后面，会报错，顺序很重要
#where要放在group by前面
#顺序：select/from/where/group by/order by

#案例四：查询有奖金的每个领导手下员工的最高工资
SELECT `manager_id` AS 领导,MAX(salary) AS 最高工资
FROM employees
WHERE `commission_pct` IS NOT NULL
GROUP BY `manager_id`; 

#添加复杂的筛选条件,添加分组后的筛选
#案例五：查询哪个部门的员工个数>2,降序排列
SELECT department_id AS 部门,COUNT((DISTINCT *) AS 员工个数
FROM employees
WHERE COUNT(DISTINCT *)>2
GROUP BY department_id
ORDER BY COUNT(DISTINCT *) DESC;


SELECT department_id,COUNT(*) AS 员工个数
FROM employees
WHERE 员工个数>2
GROUP BY department_id;

#上面两个都报错
#分步：
#1.查询每个部门的员工个数
SELECT department_id,COUNT(*) AS 员工个数
FROM employees
GROUP BY department_id;#涉及到个数，直接count(*)，不需要考虑
#2.根据1.的结果进行筛选，查询哪个部门的员工个数>2
#直接查不出来，需要根据新的结果集进行查询
SELECT department_id,COUNT(*) AS 员工个数
FROM employees
GROUP BY department_id;
#select 员工个数>2;#这段代码会报错
#------------------------------------------------------
SELECT department_id,COUNT(*) AS 员工个数
FROM employees
GROUP BY department_id
SELECT 员工个数>2;#这段代码会报错
#-------------------------------------------------------
SELECT department_id,COUNT(*) AS 员工个数
FROM employees
GROUP BY department_id
HAVING 员工个数>2;#这段结果对了
#-------------------------------------------------------
#分组后的筛选使用having+条件，对分组后生成的表进行筛选
#----------------------------------------------------------
#案例六：查询每个工种有奖金的员工的最高工资>12000工种编号和最高工资
#分两步：
#1.查询每个工种有奖金的员工的工种编号和最高工资
SELECT job_id,MAX(salary) 
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id;
#2.根据1.的结果继续进行少选，最高工资>12000
SELECT job_id,MAX(salary) AS 最高工资
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING 最高工资>12000;

#案例六：查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及其最低工资
SELECT manager_id AS 领导,ROUND(MIN(salary)) AS 手下的最低工资
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING 手下的最低工资>5000;
#在原来的表看不出结果，需要在分组后的表筛选结果就需要使用having


#按表达式/函数分组
#案例七：按员工姓名的长度进行分组，查询每一组的员工个数，筛选员工个数>5的有哪些

SELECT LENGTH(last_name) AS 姓名长度,COUNT(*) AS 员工个数
FROM employees
ORDER BY 姓名长度
HAVING 员工个数>5;#这段代码不对
#---------------------------------------------------------
#分步解决：
#1.按员工姓名的长度进行分组，查询每个长度的员工个数，
SELECT LENGTH(last_name) AS 姓名长度,COUNT(*) AS 员工个数
FROM employees
ORDER BY 姓名长度;

SELECT LENGTH(last_name) FROM employees ORDER BY LENGTH(last_name);#以上代码是错的
#-------------------------------------------------------
SELECT LENGTH(last_name) AS 姓名长度,COUNT(*) AS 员工个数
FROM employees GROUP BY 姓名长度;
#2.添加筛选条件,分组函数加到
SELECT LENGTH(last_name) AS 姓名长度,COUNT(*) AS 员工个数
FROM employees GROUP BY 姓名长度 HAVING 姓名长度>5;

SELECT LENGTH(last_name) AS 姓名长度,COUNT(*) AS 员工个数
FROM employees GROUP BY 姓名长度 HAVING 员工个数>5;

#group by /having支持别名，mysql支持，Oracle不支持
#按多个字段分组
#案例八：查询每个部门每个工种的平均工资
SELECT department_id,job_id,AVG(salary) AS 薪资水平
FROM employees
GROUP BY department_id,job_id;

#分组查询添加排序
#案例九：查询每个部门每个工种的平均工资，降序排列,分组前条件：部门编号不为null
SELECT department_id,job_id,ROUND(AVG(salary),2) AS 薪资水平
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id,job_id
HAVING 薪资水平>10000
ORDER BY 薪资水平 DESC;
#目前学到的关键词顺序：select/from/where/group by/having/order by
#-----------------------------------------------------------------------------
#案例十：
#1.
SELECT DISTINCT job_id FROM employees;#确认有重复
SELECT job_id,MAX(salary),MIN(salary),AVG(salary),SUM(salary) 
FROM employees
GROUP BY job_id
ORDER BY job_id;
#注意：选中两个句子，结果只会返回第一个句子的查询结果
#2.
SELECT MAX(salary)-MIN(salary) AS difference FROM employees;

#3.
SELECT manager_id,MIN(salary)
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary)>=6000
ORDER BY MIN(salary) DESC;
#分组函数一般直接放在having后

#4.
SELECT department_id,COUNT(*),AVG(salary)
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC;

#5.
SELECT job_id,COUNT(*) 
FROM employees
GROUP BY job_id;

#关键字顺序：select[/as]/from/where(分组前筛选，在分组前的表中)/group by/having(分组后筛选，在分组后的表中)/order by