一、概要

Group By語(yǔ)句從英文的字面意義上理解就是“根據(jù)(by)一定的規(guī)則進(jìn)行分組(Group)”。它的作用是通過一定的規(guī)則將一個(gè)數(shù)據(jù)集劃分成若干個(gè)小的區(qū)域，然后針對(duì)若干個(gè)小區(qū)域進(jìn)行數(shù)據(jù)處理。 如果在查詢的過程中需要按某一列的值進(jìn)行分組,以統(tǒng)計(jì)該組內(nèi)數(shù)據(jù)的信息時(shí),就要使用group by子句。不管select是否使用了where子句都可以使用group by子句group by子句一定要與分組函數(shù)結(jié)合使用,否則沒有意義。

二、語(yǔ)法格式

----語(yǔ)句------------------------------------------------------------執(zhí)行順序-----
SELECT [DISTINCT] * | 列名稱 [別名] , 列名稱 [別名] ,... | 統(tǒng)計(jì)函數(shù)   4、確定查詢列
FROM 數(shù)據(jù)表 [別名] , 數(shù)據(jù)表 [別名] ,...                              1、數(shù)據(jù)來源
[WHERE 條件(s)]                                                    2、過濾數(shù)據(jù)行
[GROUP BY 分組字段, 分組字段, ...]                                   3、執(zhí)行分組操作
[ORDER BY 字段 [ASC | DESC] , 字段 [ASC | DESC] ,...]               5、數(shù)據(jù)排序

三、示例代碼

1. 查詢每個(gè)部門的人數(shù)

   SELECT deptno ,COUNT(*)
   FROM emp
   GROUP BY deptno;
   

2. 顯示每個(gè)部門員工的平均工資

   SELECT deptno ,AVG(sal) 平均工資
   FROM emp
   GROUP BY deptno;
   

3. 顯示各個(gè)部門員工的工資+獎(jiǎng)金

   SELECT deptno,SUM(sal + IFNULL(comm,0))
   FROM emp
   GROUP BY deptno;
   

4. 按照部門編號(hào)分組，求出每個(gè)部門的人數(shù)，平均工資(要求截取2位)(配合單行函數(shù)使用)

   SELECT deptno, COUNT(empno), ROUND(AVG(sal),2)
   FROM emp
   GROUP BY deptno;
   

5. 按照職位分組，求出每個(gè)職位的最高和最低工資(單字段分組)

   SELECT job, MAX(sal), MIN(sal)
   FROM emp
   GROUP BY job;
   

6. 查詢每個(gè)部門的每種崗位的平均工資和最低工資

   SELECT AVG(sal), MIN(sal)
   FROM emp
   GROUP BY job;
   

7. 先統(tǒng)計(jì)出各個(gè)職位(job)的平均工資(AVG),再統(tǒng)計(jì)平均工資最高的工資(分組函數(shù)嵌套)

   SELECT MAX(AVG(sal))
   FROM emp
   GROUP BY job
   注意:分組函數(shù)允許嵌套，但是嵌套之后的分組函數(shù)的查詢之中不能再出現(xiàn)任何的其他字段
   

8. 查詢每個(gè)崗位的總工資但不包括'SALESMAN'崗位(配合Where使用)

   SELECT
   FROM emp
   WHERE name !='SALESMAN'
   

9. 按部門、不同的職位，統(tǒng)計(jì)員工的工資總額 (多字段統(tǒng)計(jì))

   SELECT deptno, job, sum(sal)
   FROM emp
   GROUP BY deptno, job;
   

10. 查詢各個(gè)部門中相同職位的員工人數(shù)并且按部門編號(hào)排序(多字段統(tǒng)計(jì)排序)

    SELECT DEPTNO, JOB,COUNT(*)
    FROM  emp
    GROUP BY deptno,job
    ORDER BY deptno;
    

四、注意事項(xiàng)

1. GROUP BY后不可以接列的別名（根據(jù)執(zhí)行順序分析就知道了）

   SELECT  deptno dn ,AVG(sal)
   FROM emp
   GROUP BY dn;  --錯(cuò)誤
   

2. GROUP BY 后不能接數(shù)字

   SELECT  deptno dn ,AVG(sal)
   FROM emp
   GROUP BY 1;   --錯(cuò)誤
   

3. GROUP BY 后不可以接select后沒有的列名

   SELECT  deptno dn ,AVG(sal)
   FROM emp
   GROUP BY job;
   

4. 如果一個(gè)SELECT中使用了分組函數(shù)，任何不在分組函數(shù)中的列(表達(dá)式)必須要在GROUP BY中

   SELECT  job ,deptno dn ,AVG(sal) --deptno列g(shù)roup by 后面沒有，使用會(huì)報(bào)錯(cuò)
   FROM emp
   GROUP BY job;
   

   筆記：3和4總結(jié)為一句話

   1、在select中出現(xiàn)的列名必須在group by 中出現(xiàn)，否則，其他列名只能在分組函數(shù)中使用;而在group by 中出現(xiàn)的字段不一定要在select中出現(xiàn)

5. group by之前可以使用where過濾數(shù)據(jù),因?yàn)閣here是在分組之前起作用的，(執(zhí)行順序分析) ----廢話

五、使用HAVING過濾分組

1、說明

1. 首先對(duì)數(shù)據(jù)行進(jìn)行分組。
2. 把所得到的分組應(yīng)用到分組函數(shù)中。
3. 最后顯示滿足having條件的記錄
   作用：在分組之后再過濾掉不符合條件的分組

2、與where的區(qū)別

1. 只有having里面可以使用分組函數(shù)，where中不允許出現(xiàn)分組函數(shù)
2. 相同作用——都是根據(jù)條件過濾數(shù)據(jù)；不同的是where是在分組之前過濾數(shù)據(jù)，having是分組之后過濾分組數(shù)據(jù)。
3. 原則：能在where里過濾的數(shù)據(jù)就不要在having里面去過濾

3、語(yǔ)法格式

----語(yǔ)句-----------------------------------------------------------執(zhí)行順序---------
SELECT [DISTINCT] * | 列名稱 [別名] , 列名稱 [別名] ,... | 統(tǒng)計(jì)函數(shù)   5、確定查詢列
FROM 數(shù)據(jù)表 [別名] , 數(shù)據(jù)表 [別名] ,...                              1、數(shù)據(jù)來源
[WHERE 條件(s)]                                                     2、過濾數(shù)據(jù)行
[GROUP BY 分組字段, 分組字段, ...]    [HIAVING 過濾分組]              3、執(zhí)行分組操作
[HAVING 條件(s)]                                                    4、過濾分組數(shù)據(jù)
[ORDER BY 字段 [ASC | DESC] , 字段 [ASC | DESC] ,...]               6、數(shù)據(jù)排序

4、示例代碼

1. 查詢部門的員工人數(shù)大于五部門編號(hào)

   SELECT deptno,COUNT(*)
   FROM emp
   GROUP BY deptno
   HAVING COUNT(*)> 5;
   

2. 查詢部門工資總和大于10000的部門編號(hào)

   SELECT deptno, SUM(sal)
   FROM emp
   GROUP BY deptno
   HAVING SUM(sal)>10000;
   

3. 查詢平均工資低于2000的部門號(hào)和它的平均工資

   SELECT deptno,AVG(sal) a
   FROM emp
   GROUP BY deptno
   HAVING avg(sal)>2000;
   

4. 查詢每個(gè)崗位的總工資并且不包括職位是'SALESMAN'崗位而且工資和大于5000

   SELECT SUM(sal)
   FROM emp
   WHERE job!='SALESMAN'
   GROUP BY job  HAVING SUM(sal)>5000
   

六、綜合示例

1. 查詢非銷售人員工作名稱以及從事同一工作雇員的月工資的總和，并且要滿足從事同一工作的雇員的月工資合計(jì)大于$5000，輸出結(jié)果按月工資的合計(jì)升序排列
   1、查詢出所有的非銷售人員的信息

   SELECT * FROM emp WHERE job!=SALESMAN';
   

   2、按照職位進(jìn)行分組，并且使用SUM函數(shù)統(tǒng)計(jì)

   SELECT job,SUM(sal)
   FROM emp
   WHERE job<>'SALESMAN'
   GROUP BY job;
   

   3、月工資的合計(jì)是通過統(tǒng)計(jì)函數(shù)查詢的，所以現(xiàn)在這個(gè)對(duì)分組后的過濾要使用HAVING子句完成

   SELECT job,SUM(sal)
   FROM emp
   WHERE job!='SALESMAN'
   GROUP BY job
   HAVING SUM(sal)>5000;
   

   4、按照升序排列

   SELECT job,SUM(sal) sum
   FROM emp
   WHERE job!='SALESMAN'
   GROUP BY job
   HAVING SUM(sal)>5000
   ORDER BY sum ASC;
   

2. 顯示部門編號(hào)不是30的，的部門詳細(xì)信息（部門編號(hào)、部門名稱、部門人數(shù)、部門月薪資總和），并要求 部門月工資總和大于8000，輸出結(jié)果按部門月薪資的總和降序排列。

   SELECT d.deptno,d.dname,COUNT(*) 人數(shù),ifnull(SUM(e.sal),0) 月總收入
   FROM dept d,emp e
   WHERE d.deptno=e.deptno AND d.deptno!=30
   GROUP BY d.deptno,d.dname
   HAVING SUM(e.sal) >8000
   ORDER BY SUM(e.sal) DESC;
   或
   select deptno,d.dname ,count(*) peonum,sum(e.sal) s
   from dept d left join emp e using(deptno)  --注意:using()中的字段在使用時(shí)不能有前綴。
   where deptno !=30
   group by deptno ,d.dname
   having sum(e.sal)>8000
   order by s desc;
   

七、性能問題

能在where能過濾數(shù)據(jù)不要在having里過濾，A和B都能達(dá)到同樣的目的，但是A性能相對(duì)好一些，因?yàn)锳現(xiàn)將deptno=30的數(shù)據(jù)篩選出來，然后在將篩選的數(shù)據(jù)放入到臨時(shí)表空間內(nèi)進(jìn)行分組；而B將全部的數(shù)據(jù)都讀到臨時(shí)表空間內(nèi)，然后在臨時(shí)表空間進(jìn)行篩選數(shù)據(jù)，這樣一來B就需要更大的臨時(shí)表空間進(jìn)行分組篩選，索引性能較差。