mysql 分组查询及筛选

# 分组查询
/*
select 分组函数,列(要求出现在group by的后面)
from table
[where condition]
[group by group_by_expression]
[order by column];

注意：查询列表必须是分组函数和group by 后出现的字段

特点：
                 数据源            位置          关键字
    分组前筛选：  原始表         group by子句的前面   where
    分组后筛选：  分组后的结果集     group by子句的后面   having
    
    分组函数做条件肯定是放在having子句中
    能用分组前筛选的，就优先考虑分组前筛选
    3. group by支持单个字段分组，多个字段分组（多个字段之间用逗号隔开没有顺序要求）
       表达式和函数（用的较少）
    4. 也可以添加排序（排序放在整个分组查询的最后）   
*/  

# 每个工种的最高工资
SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;

#每个位置上的部门个数 (“每个”后面是分组的字段)
SELECT COUNT(department_id),location_id
FROM departments
GROUP BY location_id;

#添加 分组前的 筛选
#查询邮箱中包含a字符的，每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;

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

#添加 分组后的 筛选条件
#查询那个部门的员工个数大于2
# 查询每个部门的员工个数
SELECT COUNT(*) AS c, department_id
FROM employees
GROUP BY department_id


SELECT COUNT(*) AS c, department_id
FROM employees
GROUP BY department_id
HAVING c>2;

#查询每个工种有奖金的员工的最高工资>12000工种编号和最高工资
#1 查询每个工种有奖金的员工的最高工资
SELECT job_id,MAX(salary) m
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id;
#2. 根据1的结果继续筛选最高工资>12000
SELECT job_id,MAX(salary) m
FROM employees
GROUP BY job_id
HAVING m>12000;

#查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及最低工资
# 1 查询每个领导手下的员工固定最低工资
SELECT manager_id,MIN(salary) m
FROM employees
GROUP BY manager_id;

#2 添加筛选条件：编号>102
SELECT manager_id,MIN(salary) m
FROM employees
WHERE manager_id>102
GROUP BY manager_id;

#3 添加筛选条件：最低工资>5000
SELECT manager_id,MIN(salary) m
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING m>5000;


#按表达式户或函数分组
#按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
#1 每个长度的员工个数
SELECT LENGTH(last_name) len,COUNT(*)
FROM employees
GROUP BY len;

#添加筛选条件：员工个数>5
SELECT LENGTH(last_name) len,COUNT(*) c
FROM employees
GROUP BY len
HAVING c>5;

#按多个字段分组
#查询每个部门每个工种的平均工资
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id,job_id;

#添加排序
#查询每个部门每个工种的平均工资，并且按平均工资的高低显示
SELECT department_id,job_id,AVG(salary) a
FROM employees
GROUP BY department_id,job_id
ORDER BY a DESC;

SELECT department_id,job_id,AVG(salary) a
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id,job_id
ORDER BY a DESC;


SELECT department_id,job_id,AVG(salary) a
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id,job_id
HAVING a>10000
ORDER BY a DESC;

# 查询各个job_id的工资的最大 最小 平均 总和 并按job_id升序
SELECT job_id, MAX(salary), MIN(salary),AVG(salary), SUM(salary)
FROM employees
GROUP BY job_id
ORDER BY job_id ASC;


# 查询员工最高工资和最低工资的差距
SELECT MAX(salary) ma,MIN(salary) mi, MAX(salary)-MIN(salary) diffrence
FROM employees;


#查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
SELECT manager_id, MIN(salary) m
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING m>=6000;

#查询所有部门的编号，员工数量 工资平均值 并按平均工资排序
SELECT manager_id, COUNT(*), AVG(salary) s
FROM employees
GROUP BY manager_id
ORDER BY s;

#选择具有job_id的员工人数
SELECT job_id, COUNT(*)
FROM employees
GROUP BY job_id;