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;