MYSQL GROUP BY 和 HAVING 子句

聚合函數

        在介紹GROUP BY 和HAVING子句前，我們先講講sql語言中的一種特殊的函數:聚合函數.

例如SUM,COUNT,MAX,AVG等。這些函數和其他函數的根本差別就在于它們一般作用于多個紀錄上。

select SUM(population) FROM bbc;

這裡的SUM作用在所有傳回記錄的population字段上，結果就是該查詢隻傳回一個結果，即所有 國家的總人口數。 

       通過使用GROUP BY子句，可以讓SUM和COUNT這些函數對屬于一組的資料起作用。當你指定GROUP BY region時，屬于同一個region(地區)的一組資料将隻能傳回一行值。也就是說，表中所有出region(地區)外的字段，隻能通過SUM,COUNT等聚合函數運算後傳回一個值。

       HAVING子句可以讓我們篩選成組後的各組資料。

       WHERE子句在聚合前線篩選紀錄，也就是說作用在GROUP BY子句和HAVING子句前。而HAVING子句在聚合後對組紀錄進行篩選。

SQL執行個體:

一、顯示各個地區的總人口數和總面積

SELECT region,SUM(population),SUM(area)
FROM bbc
GROUP BY region; 
           

先以region把傳回紀錄分成多個組，這就是GROUP BY的字面含義。分完組後，然後用聚合函數對每個組中的不同字段(一或多條紀錄)作運算。

二、顯示每個地區的總人口數和總面積，僅顯示那些面積超過1000 000的地區。

SELECT region,SUM(population),SUM(area)
FROM bbc
GROUP BY region
HAVING SUM(area) > 1000000;
           

在這裡，我們不能用where來篩選超過1000000的底氣，因為表中不存在這樣一條紀錄。相反，HAVING子句可以讓我們篩選成組後的各組資料。

GROUP BY 用法解析 

group by文法可以根據給定資料列的每個成員對查詢結果進行分組統計，最終得到一個分組彙總表。

SELECT子句中的列名必須為分組列或列函數。列函數對于GROUP BY子句定義的每個組各傳回一個結果。

某個員工資訊表結構和資料如下：

例如，我想列出每個部門最高薪水的結果

SELECT DEPT, MAX(SALARY) AS MAXIMUM
FROM STAFF
GROUP BY DEPT;
           

查詢結果如下：

      DEPT  MAXIMUM 

      開發部 2500

      設計部 2600

      銷售部 3500

解釋一下結果：

1、滿足“SELECT子句中的列名必須為分組列或列函數”，因為SELECT有GROUP BY DEPT中包含的列DEPT。

2、“列函數對于GROUP BY子句定義的每個組各傳回一個結果”，根據部門分組，對每個部門傳回一個結果，就是每個部門的最高薪水。

注意：計算的是每個部門（由 GROUP BY 子句定義的組）而不是整個公司的 MAX(SALARY)。

例如，查詢每個部門的總的薪水數

SELECT DEPT, sum( SALARY ) AS total
FROM STAFF
GROUP BY DEPT;
           

查詢結果如下：

DEPT  total 

開發部 4500

設計部 7000

銷售部 9600

将 WHERE 子句與 GROUP BY 子句一起使用

分組查詢可以在形成組和計算列函數之前具有消除非限定行的标準 WHERE 子句。必須在GROUP BY 子句之前指定 WHERE 子句。

例如，查詢公司2010年入職的各個部門每個級别裡的最高薪水

SELECT DEPT, delevel, MAX( SALARY ) AS MAXIMUM
FROM staff
WHERE HIREDATE > '2014-01-01'
GROUP BY DEPT, delevel
ORDER BY DEPT, delevel;
           

查詢結果如下：

  DEPT  EDLEVEL  MAXIMUM 

      設計部 4 2300

      設計部 5 2600

      銷售部 5 3000

      銷售部 7 3500

注意：在SELECT語句中指定的每個列名也在GROUP BY子句中提到。未在這兩個地方提到的列名将産生錯誤。

GROUP BY子句對DEPT和EDLEVEL的每個唯一組合各傳回一行。

在GROUP BY子句之後使用HAVING子句

可應用限定條件進行分組，以便系統僅對滿足條件的組傳回結果。為此，在GROUP BY子句後面包含一個HAVING子句。HAVING子句可包含一個或多個用AND和OR連接配接的謂詞。每個謂詞将組特性（如AVG(SALARY)）與下列之一進行比較：

例如：尋找雇員數超過2個的部門的最高和最低薪水：

SELECT DEPT, MAX( SALARY ) AS MAXIMUM, MIN( SALARY ) AS MINIMUM
FROM staff
GROUP BY DEPT
HAVING COUNT( * ) >2
ORDER BY DEPT;
           

查詢結果如下：

  DEPT  MAXIMUM  MINIMUM 

      設計部 2600 2100

      銷售部 3500 3000

例如：尋找雇員平均工資大于3000的部門的最高和最低薪水：

SELECT DEPT, MAX( SALARY ) AS MAXIMUM, MIN( SALARY ) AS MINIMUM
FROM staff
GROUP BY DEPT
HAVING AVG( SALARY ) >3000
ORDER BY DEPT;
           

查詢結果如下：

  DEPT  MAXIMUM  MINIMUM 

      銷售部 3500 3000