SQL分组查询

#SQL分组查询

#分组函数
SELECT SUM(`salary`) FROM `employees`; #工资总额
SELECT AVG(`salary`) FROM `employees`;
SELECT MIN(`salary`) FROM `employees`;
SELECT MAX(`salary`) FROM `employees`; #工资最大值
SELECT COUNT(`salary`) FROM `employees`; #计数
SELECT 
  MAX(`salary`) 最大,
  MIN(`salary`) 最小,
  ROUND(AVG(`salary`), 2) 平均  # 保留两位小数
FROM
  `employees` ;

#和distinct 的搭配,意思是去除重复的意思
SELECT SUM(DISTINCT `salary`),`salary` FROM `employees`;

#count函数的详细介绍
SELECT COUNT(*) FROM `employees`; #常常用来统计总行数，因为count 统计遇到null不计数，所以一般用*
SELECT COUNT(1) FROM `employees`; #也用来统计列的个数 加常量值 效果一样 2 1 都可以

#和分组查询的字段有限制,一般是group by后的字段
SELECT SUM(`salary`),`salary` FROM `employees`;

#查询部门编号为90的员工个数
SELECT COUNT(*) FROM `employees` WHERE `department_id` = 90;

#查询员工表中入职时间差距最大时间 DATEDIFF 时间差距
SELECT DATEDIFF(MAX(`hiredate`),MIN(`hiredate`)) FROM `employees`;

#分组查询

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

#查询每个位置上的部门个数
SELECT 
  COUNT(*),
  `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` ;

#添加复杂的筛选条件
#哪个部门的员工个数大于二，1.先查询每个部门的员工个数 
#2.用1的结果找到那个部门员工个数大于2
SELECT 
  COUNT(*),
  `department_id` 
FROM
  `employees` 
GROUP BY `department_id` 
HAVING COUNT(*) > 2 ;

#查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT 
  MAX(`salary`),
  `job_id` 
FROM
  `employees` 
WHERE `commission_pct` IS NOT NULL
GROUP BY `job_id` 
HAVING MAX(`salary`) > 12000 ;

#查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，及其最低工资
SELECT 
  MIN(`salary`),
  `manager_id` 
FROM
  `employees` 
WHERE `manager_id` > 102 
GROUP BY `manager_id` 
HAVING MIN(`salary`) > 5000 ;

#按员工的姓名长度分组，查询每一组员工的个数，筛选员工个数>5的有哪些
SELECT COUNT(*),`last_name` AS 名字 ,LENGTH(`last_name`)
FROM `employees`
GROUP BY LENGTH(`last_name`)
HAVING COUNT(*)>5;

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

#添加排序
#查询每个部门每个工种的员工的平均工资，并且按平均工资的高低排序
SELECT 
  AVG(`salary`),
  `department_id`,
  `job_id` 
FROM
  `employees` 
WHERE `department_id` IS NOT NULL
GROUP BY `department_id`, `job_id` 
HAVING AVG(`salary`)>10000
ORDER BY  AVG(`salary`) DESC;