SELECT数据查询5——groupby分组查询语法格式：
select 分组函数，列(要求出现在group by的后⾯)
from 表
【where】筛选条件
group by 分组列表
【order by ⼦句】
注意：
查询列表必须特殊，要求是分组函数和group by 后出现的字段
总结特点：
1、分组查询中的筛选条件分为两类
数据库位置关键字
分组前筛前原始表            group by⼦句前⾯    where
分组筛选后分组后的结果集    group by⼦句后⾯    having
(1)分组函数做条件⼀定是放在having⼦句中
(2)能⽤分组前筛选的，就优先考虑使⽤分组前筛选
2、group by⼦句⽀持单个⼦句字段分组，多个字段分组（逗号），
表达式或函数
3、可以添加排序，放在最后
下⾯例题使⽤到的数据：
employees 员⼯表
salary job_id commission_pct departemt_id last_name manager_id
⼯资列⼯资列奖⾦率列部门编号员⼯名列领导编号列
departments 部门表
department_id department_name manager_id location_id
部门标号列部门名称列领导编号列位置编号列
#引⼊：查询每个部门的平均⼯资
SELECT AVG(salary)FROM employees;
#简单分组查询题
#1、查询每个⼯种的最⾼⼯资
SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;
#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;
#添加复杂(分组后)的筛选条件
1、查询哪个部门的员⼯个数>2
#(1)查询每个员⼯的个数
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id;
#(2)根据(1)的结果进⾏筛选，查询哪个部门的员⼯数>2  having
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2
ORDER BY COUNT(*);
2、查询每个⼯种有奖⾦的员⼯的最⾼⼯资>12000的⼯种编号和最⾼⼯资
#(1)查询每个⼯种有奖⾦的员⼯的最⾼⼯资
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id;groupby是什么函数
#(2)根据(1)结果继续筛选，最⾼⼯资>12000
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;
3、查询领导编号>120的每个领导⼿下的最低⼯资>5000的领导编号是哪个，以及其最低⼯资#(1)查询每个领导
SELECT MIN(salary),manager_id
FROM employees
GROUP BY manager_id;
#(2)添加筛选条件：领导编号>120
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id;
#(3)再添加筛选条件：最低⼯资>5000
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000
ORDER BY MIN(salary);
#按表达式或函数分组
按员⼯姓名的长度分组，查询每⼀组的员⼯个数，筛选员⼯个数>5的有哪些#(1)查询每个长度的员⼯个数
SELECT COUNT(*),LENGTH(last_name) len_name
FROM employees
GROUP BY LENGTH(last_name);
#(2)添加筛选条件
SELECT COUNT(*),LENGTH(last_name) len_name
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*)>5;
#使⽤别名
SELECT COUNT(*)AS a,LENGTH(last_name) len_name
FROM employees
GROUP BY LENGTH(last_name)
HAVING a>5;
#按多个字段分组
#查询每个部门每个⼯种的员⼯的平均⼯资
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY job_id,department_id;
#综合
SELECT AVG(salary),department_id,job_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY job_id,department_id
HAVING AVG(salary)>10000
ORDER BY AVG(salary)DESC;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。