mysql分组查询

mysql分组查询

分组查询

/*
语法：

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

注意1：where和from是亲戚，他们必须挨着
注意2：查询列表比较特殊，要求是分组函数和order by后出现的字段

特点：
筛选分为两类：分组前筛选和分组后筛选
                               针对的表                             位置                连接的关键字
分组前筛选               原始表                          group by前                where
分组后筛选        group by后的结果集            group by后                having

一般来讲，能用分组前筛选的，尽量使用分组前筛选，提高效率
分组函数做条件肯定是放在having子句中
分组函数做条件时不能放在where后面
group by子句支持单个字段分组，多个字段分组(多个字段之间用,逗号隔开，没有顺序要求)，表达式或函数(用的比较少)
可以搭配着排序使用(排序放在整个分组查询的最后)
*/

直接po代码，代码中有注释

#五、分组查询
/*
语法：

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

注意1：where和from是亲戚，他们必须挨着
注意2：查询列表比较特殊，要求是分组函数和order by后出现的字段

特点：
筛选分为两类：分组前筛选和分组后筛选
			针对的表			   位置		连接的关键字
分组前筛选	         原始表			group by前	   where
分组后筛选	    group by后的结果集    	group by后	   having

一般来讲，能用分组前筛选的，尽量使用分组前筛选，提高效率
分组函数做条件肯定是放在having子句中
分组函数做条件时不能放在where后面
group by子句支持单个字段分组，多个字段分组(多个字段之间用,逗号隔开，没有顺序要求)，表达式或函数(用的比较少)
可以搭配着排序使用(排序放在整个分组查询的最后)


*/
#查询每个部门的平均工资
SELECT 
  department_id AS 部门编号,
  ROUND(AVG(salary), 2) AS 平均薪水
FROM
  employees 
GROUP BY department_id ;

#按部门编号分组
SELECT 
  department_id 
FROM
  employees 
GROUP BY department_id ;
#每个部门有多少人
SELECT 
  department_id,
  COUNT(*) 
FROM
  employees 
GROUP BY department_id ;

#GROUP BY分组
SELECT 
  AVG(salary),
  department_id 
FROM
  employees 
GROUP BY department_id ;

#简单的分组查询
#案例1：查询每个工种的员工平均工资
SELECT 
  MAX(salary) AS 最高薪水,
  job_id AS 工种 
FROM
  employees 
GROUP BY job_id ;

#
DESC departments;

#案例2：查询每个位置的部门个数
SELECT 
  COUNT(*),
  location_id 
FROM
  departments 
GROUP BY location_id ;

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

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

#添加分组后的筛选条件
SELECT 
  COUNT(*) AS cou,
  department_id 
FROM
  employees 
GROUP BY department_id 
HAVING COUNT(*) > 2 ;
#
SELECT 
  COUNT(*) AS cou,
  department_id 
FROM
  employees 
GROUP BY department_id 
HAVING cou > 2 ;

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

/*

注意：
如果要筛选GROUP BY后面跟的字段，既可以使用where对该字段进行筛选，也可
以使用having对该字段进行筛选,个人建议优先选用where，原
因是一般来讲，能用分组前筛选的，尽量使用分组前筛选，提高效率

*/
#一般来讲，能用分组前筛选的，尽量使用分组前筛选，提高效率
#案例3：领导编号>102的每个领导手下的最低工资大于6000的领导编号和最低工资
SELECT 
  manager_id,
  MIN(salary) AS minMoney 
FROM
  employees 
WHERE manager_id > 102  #manager_id放在WHERE子句中
GROUP BY manager_id 
HAVING minMoney > 6000 ;

#一般来讲，能用分组前筛选的，尽量使用分组前筛选，提高效率
SELECT 
  manager_id,
  MIN(salary) AS minMoney 
FROM
  employees 
GROUP BY manager_id 
HAVING minMoney > 6000 AND manager_id > 102; #也可以把manager_id放在HAVING子句中

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

#GROUP BY后面和HAVING后面都支持别名，如下：
SELECT 
  LENGTH(last_name) AS len_name,
  COUNT(*) AS cou
FROM
  employees 
GROUP BY len_name
HAVING cou > 5 ;

#按多个字段进行分组
#案例查询每个部门的每个工种的员工的平均工资
SELECT 
  department_id,
  job_id,
  AVG(salary) 
FROM
  employees 
GROUP BY department_id,
  job_id ; #指定多个分组列，大组中再分小组
  
#按多个字段进行分组，GROUP BY后面的字段之间，可以调换顺序，效果一样
SELECT 
  department_id,
  job_id,
  AVG(salary) 
FROM
  employees 
GROUP BY job_id,
  department_id ; #指定多个分组列，大组中再分小组
  
#分组后排序
SELECT 
  department_id,
  job_id,
  AVG(salary) 
FROM
  employees 
GROUP BY job_id,
  department_id 
ORDER BY department_id ASC ;
 
##分组后排序
SELECT 
  department_id,
  job_id,
  AVG(salary) 
FROM
  employees 
GROUP BY department_id,
  job_id 
ORDER BY department_id ASC ;

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

/*
部门不为null,可以放在WHERE子句后面(建议放在WHERE子句后面)
*/
SELECT 
  department_id,
  job_id,
  AVG(salary) AS avg_money 
FROM
  employees 
  WHERE department_id IS NOT NULL
GROUP BY department_id,
  job_id 
ORDER BY avg_money DESC ;

/*
部门不为null,也可以放在HAVING子句后面(不建议放在HAVING子句后面，建
议放在WHERE子句后面，放在WHERE子句后面效率更高)*/
SELECT 
  department_id,
  job_id,
  AVG(salary) AS avg_money 
FROM
  employees 
GROUP BY department_id,
  job_id 
HAVING department_id IS NOT NULL  
ORDER BY avg_money DESC ;

#平均工资高于12000的
SELECT 
  department_id,
  job_id,
  AVG(salary) AS avg_money 
FROM
  employees 
  WHERE department_id IS NOT NULL
GROUP BY department_id,
  job_id
  HAVING  avg_money > 12000
ORDER BY avg_money DESC ;


#根据表达式分组
SELECT 
  YEAR(hiredate),
  COUNT(*) 
FROM
  employees 
GROUP BY YEAR(hiredate);

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

#2.查询员工最高工资和最低工资的差距(DIFFERENCE)
SELECT 
  MAX(salary) AS "max",
  MIN(salary) AS 'min',
  (MAX(salary) - MIN(salary)) AS DIFFERENCE 
FROM
  employees ;

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

#4.查询所有部门的编号，员工数量和工资平均值,并按平均工资降序
SELECT 
  department_id,
  COUNT(*),
  AVG(salary) AS avg_Money 
FROM
  employees 
GROUP BY department_id 
ORDER BY avg_Money DESC ;

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


#使用WITH ROLLUP
SELECT 
  YEAR(hiredate),
  COUNT(*) 
FROM
  employees 
GROUP BY YEAR(hiredate) WITH ROLLUP;

#使用WITH ROLLUP
SELECT 
  LENGTH(last_name) AS len_name,
  COUNT(*) AS cou,
  GROUP_CONCAT(last_name)
FROM
  employees 
GROUP BY len_name WITH ROLLUP 
HAVING cou > 5 ;

#使用WITH ROLLUP
SELECT 
  job_id
 # GROUP_CONCAT(job_id) 
FROM
  employees 
GROUP BY job_id WITH ROLLUP ;

#
SELECT 
  job_id,
 GROUP_CONCAT(job_id) 
FROM
  employees 
GROUP BY job_id WITH ROLLUP ;
#GROUP_CONCAT()函数
SELECT 
  department_id,
  GROUP_CONCAT(department_id) AS dep,
  GROUP_CONCAT(employee_id) AS emp
FROM
  employees 
GROUP BY department_id ;

#如果没有group by子句，group_concat返回一列的所有值
SELECT GROUP_CONCAT(job_id) FROM employees;