/*
語(yǔ)法：
select 查詢(xún)列表
from 表
【where 篩選條件】
group by 分組的字段
【order by 排序的字段】;
特點(diǎn)：
1、和分組函數(shù)一同查詢(xún)的字段必須是group by后出現(xiàn)的字段
2读串、篩選分為兩類(lèi)：分組前篩選和分組后篩選
針對(duì)的表 位置 連接的關(guān)鍵字
分組前篩選 原始表 group by前 where
分組后篩選 group by后的結(jié)果集 group by后 having
問(wèn)題1：分組函數(shù)做篩選能不能放在where后面
答：不能
問(wèn)題2：where——group by——having
一般來(lái)講，能用分組前篩選的割疾，盡量使用分組前篩選毁渗，提高效率
3、分組可以按單個(gè)字段也可以按多個(gè)字段
4、可以搭配著排序使用
*/
1.簡(jiǎn)單的分組
案例1：查詢(xún)每個(gè)工種的員工平均工資
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;
案例2：查詢(xún)每個(gè)位置的部門(mén)個(gè)數(shù)
SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;
2照棋、可以實(shí)現(xiàn)分組前的篩選
案例1：查詢(xún)郵箱中包含a字符的 每個(gè)部門(mén)的最高工資
SELECT MAX(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;
案例2：查詢(xún)有獎(jiǎng)金的每個(gè)領(lǐng)導(dǎo)手下員工的平均工資
SELECT AVG(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;
3、分組后篩選
案例：查詢(xún)哪個(gè)部門(mén)的員工個(gè)數(shù)>5
①查詢(xún)每個(gè)部門(mén)的員工個(gè)數(shù)
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id;
② 篩選剛才①結(jié)果
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>5;
案例2：每個(gè)工種有獎(jiǎng)金的員工的最高工資>12000的工種編號(hào)和最高工資
SELECT job_id,MAX(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;
案例3：領(lǐng)導(dǎo)編號(hào)>102的每個(gè)領(lǐng)導(dǎo)手下的最低工資大于5000的領(lǐng)導(dǎo)編號(hào)和最低工資
manager_id>102
SELECT manager_id,MIN(salary)
FROM employees
GROUP BY manager_id
HAVING MIN(salary)>5000;
4.添加排序
案例：每個(gè)工種有獎(jiǎng)金的員工的最高工資>6000的工種編號(hào)和最高工資,按最高工資升序
SELECT job_id,MAX(salary) m
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING m>6000
ORDER BY m ;
5.按多個(gè)字段分組
案例：查詢(xún)每個(gè)工種每個(gè)部門(mén)的最低工資,并按最低工資降序
SELECT MIN(salary),job_id,department_id
FROM employees
GROUP BY department_id,job_id
ORDER BY MIN(salary) DESC;