SQL查詢入門（下篇）

引言

      在前兩篇文章中，對于單表查詢和多表查詢的概念做出了詳細的介紹，在本篇文章中會主要介紹聚合函數的使用和數據的分組.

簡介

      簡單的說，聚合函數是按照一定的規則將多行(Row)數據匯總成一行的函數。對數據進行匯總前，還可以按照特定的列(column)將數據進行分組(Group by)再匯總,然后按照再次給定的條件進行篩選(Having).

      聚合函數將多行數據進行匯總的概念可以簡單用下圖解釋:

簡單聚合函數

       簡單聚合函數是那些擁有很直觀將多行(Row)匯總為一行(Row)計算規則的函數。這些函數往往從函數名本身就可以猜測出函數的作用，而這些函數的參數都是數字類型的。簡單聚合函數包括:Avg,Sum,Max,Min.

       簡單聚合函數的參數只能是數字類型，在SQL中，數字類型具體包括:tinyint,smallint,int,bigint,decimal,money,smallmoney,float,real.

       在介紹簡單聚合函數之前，先來介紹一下Count()這個聚合函數.

      Count()

       Count函數用于計算給定條件下所含有的行(Row)數.例如最簡單的:

       上表中，我想知道公司員工的個數，可以簡單的使用:

SELECT     COUNT(*) AS EmployeeNumber
FROM         HumanResources.Employee

        結果如下

         當Count()作用于某一特定列(Column)，和以“*”作為參數時的區別是當Count(列名)碰到“Null”值時不會將其計算在內,例如:

        我想知道公司中有上級的員工個數:

SELECT     COUNT(ManagerID) AS EmployeeWithSuperior
FROM       HumanResources.Employee

       可以看到，除了沒有上級的CEO之外，所有其他的員工已經被統計在內.

       也可以在Count()內使用Distinct關鍵字來讓,每一列（Column）的每個相同的值只有一個被統計在內，比如:

       我想統計公司中經理層級的數量：

SELECT     COUNT(DISTINCT ManagerID) AS NumberOfManager
FROM       HumanResources.Employee

       結果如上.

        Avg(),Sum(),Max()和Min()

        這幾個聚合函數除了功能不同以外，參數和用法幾乎相同。所以這里只對Avg()這個聚合函數進行解釋:

        Avg()表示計算在選擇范圍內的匯總數據的平均值.這個過程中“Null”值不會被統計在內 ,例如:

        我想獲得平均每位員工休假的時長:

SELECT     AVG(VacationHours) AS 'Average vacation hours'
FROM       HumanResources.Employee

        結果如下:

      因為默認用聚合函數進行數據匯總時，不包含null,但如果我想要包含null值，并在當前查詢中將Null值以其他值替代并參與匯總運算時，使用IsNull(column,value)

      例如:

      我想獲得平均每位員工的休假時長，如果員工沒有休假，則按休假10個小時計算

SELECT     AVG(ISNULL(VacationHours, 10)) AS  'Average vacation hours'
FROM       HumanResources.Employee

      結果如下:

     也可以使用DISTINCT關鍵字在簡單聚合函數中讓每一個值唯一參與聚合匯總運算.在上面的Count函數中已經解釋，這里不做重復。

     而關于Sum(),Max(),Min()等這些簡單聚合函數，使用方法基本相同，這里就不重復了

將聚合函數得到的值按照列(Column)進行分組

      如果聚合函數所得到的結果無法按照特定的值進行分組，那聚合函數的作用就沒那么強了。在SQL中，使用Group by對聚合函數匯總的值進行分組。分組的概念可以下面這個簡單的例子表示：

       例如：

       我想根據不同省得到銷售人員所銷售的總和:

SELECT     TerritoryID, SUM(SalesLastYear) AS ToTalSales
FROM       Sales.SalesPerson
GROUP BY   TerritoryID

      概念如下圖所示:

   跟在Group by后面的列名是分組的依據。當然在某些情況下，會有依據多個列(Column)進行分組的情況.下面這個例子有點實際意義:

   我想按照不同性別獲得不同經理手下的員工的病假時間總和:

SELECT     ManagerID, Gender, SUM(SickLeaveHours) AS SickLeaveHours, COUNT(*) AS EmployeeNumber
FROM       HumanResources.Employee
GROUP BY   Gender, ManagerID

   結果如下:

     Group By后面多列，我們可以在邏輯思維上這么想，先根據每一列唯一的ManagerId和唯一的Gender進行Cross Join(如果你不懂什么Cross join,請看我前面的文章)得到唯一可以確定其他鍵(Key)的鍵,最后過濾掉聚合函數中不能返回值的行(Row）（也就是為Null)的行。再根據這實際上兩列，但邏輯上是一列的值作為分組依據。

     上圖中可以看到，我們首先按照經理ID,進行分組，然后根據不同經理手下的員工的性別，再次進行分總，最終按照這個分組條件得到病假時間總和.

     這里要注意，當使用Group By按照多列(Column)進行分組時，一定要注意出現在Group By后面的次序

     上面先出現Gender是先遍歷Gender的所有可能的值，再根據每個Gender可能的值去計算匹配ManagerID，最后再根據ManagerID來進行聚合函數運算，如果將上面Group By后面得列(Column)順序改為先ManagerId,再Gender,則意味著先遍歷ManagerID所有可能出現的值，再去匹配Gender，則結果如下:

     從Gender（性別）變為M（男性）開始，第二次遍歷ManagerId進行匹配:

     從上面我們可以看出，雖然Group by后面出現列（Column）的次序不同，所得到結果的順序也不同，但所得到的數據集(DataSet)是完全一樣，所以，可以通過Order By子句將按照不同列次序進行Group By的查詢語句獲得相同的結果。這里就不再截圖了。

對分組完成后的數據集進行再次篩選（Having） 

      當對使用聚合函數進行分組后，可以再次使用放在Group By子句后的Having子句對分組后的數據進行再次的過濾.Having子句在某些方面很像Where子句，具體having表達式的使用可以看我前面文章中對where的講解。Having子句可以理解成在分組后進行二次過濾的語句.

      使用having子句非常簡單，但需要注意的是，having子句后面不能跟在select語句中出現的別名，而必須將Select語句內的表達式再寫一遍，例如還是針對上面的表：

      我想按照不同性別獲得不同經理手下的員工的病假時間總和，這些經理手下的員工需要大于2個人:

SELECT     ManagerID, Gender, SUM(SickLeaveHours) AS SickLeaveHours, COUNT(*) AS EmployeeNumber
FROM       HumanResources.Employee
GROUP BY   ManagerID, Gender
HAVING     (EmployeeNumber > 2)

      注意，上面這句話是錯誤的,在Having子句后面不能引用別名或者變量名，如果需要實現上面那個效果，需要將Count(*)這個表達式再Having子句中重寫一遍,正確寫法如下：

SELECT     ManagerID, Gender, SUM(SickLeaveHours) AS SickLeaveHours, COUNT(*) AS EmployeeNumber
FROM       HumanResources.Employee
GROUP BY   ManagerID, Gender
HAVING     (COUNT(*) > 2)

       結果如下：

      我們看到，只有員工數大于2人的條件被選中。

      當然，Having子句最強大的地方莫過于其可以使用聚合函數作為表達式，這是在Where子句中不允許的。下面這個例子很好的演示了Having子句的強大之處：

      還是上面那個例子的數據：

      我想獲得不同經理手下的員工的病假時間總和，并且這個經理手下病假最多的員工的請假小時數大于病假最少員工的兩倍：

SELECT     ManagerID, SUM(SickLeaveHours) AS TotalSickLeaveHours, COUNT(*) AS EmployeeNumber
FROM       HumanResources.Employee
GROUP BY   ManagerID
HAVING      (MAX(SickLeaveHours) > 2 * MIN(SickLeaveHours))

     結果如下：

     這里可以看出，Having子句實現如此簡單就能實現的強大功能，如果用where將會非常非常的麻煩。上面那個結果中，having語句聚合函數的作用范圍可以用下圖很好的演示出來:

     上面可以看出被篩選后的數據滿足請假最多員工的小時數明顯大于請假最少員工小時數的兩倍。

小結

      本文以聚合函數概念為開始，講述了聚合函數使用中經常用到的查詢，分組，過濾的概念和使用方式。使用好聚合函數可以將很多放到應用程序業務層的任務轉到數據庫里來.這會對維護和性能提升很很大的幫助.

PS：SQL查詢入門就寫完了，雖然文章講的內容很淺，但我在寫作的過程中腦中很多模糊的概念變得逐漸清晰。從而對自己也是一次再學習。我也努力將每一個概念以簡單的方式表現出來。技術文章就應該這樣吧:-) 后續文章準備中ing….