MySQL分组筛选

#分组函数按表达式或函数分组
#按员工姓名的长度分组，查询每一组的员工个数，
#筛选员工个数>5的有哪些。

SELECT
COUNT(*),LENGTH(last_name)
FROM
employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*)>5;

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

# 查询每个部门每个工种的员工的平均工资,并且按平均工资高低显示

SELECT
AVG(salary),job_id,department_id
FROM
employees
GROUP BY department_id,job_id
ORDER BY AVG(salary) DESC;

#分组查询的语法
#select
#from
#where
#group by
#having
#order by

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

#查询员工最高工资和最低工资的差距（difference）
SELECT
MAX(salary),MIN(salary),MAX(salary)-MIN(salary) difference,job_id
FROM
employees
GROUP BY job_id;

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

 

#查询所有部门的编号，员工数量和工资平均值，并按平均工资降序。

SELECT
COUNT(*),
department_id,
AVG(salary)
FROM
employees
GROUP BY department_id
ORDER BY AVG(salary) DESC;
#选择其有各个job_id的员工人数。
SELECT
job_id,COUNT(*)
FROM
employees
GROUP BY
job_id;