最近要完成一個項目，有一個查詢可難住了筆者，無論是子查詢還是分組，都沒弄出來， 還是基礎知識不行啊。不過呢，可以查資料，最後用一個窗口函數解決了問題。由於開始的 數據庫是Access，後來筆者導成SQL Server的，一下子明白了很多。
數據庫類似是這個樣子滴：
Employee表的字段：
empId,englishName,depId
Department表的字段：
depId,deptName
需求是：查找出Department表的所有字段，但是在前面顯示出該部門的人數。
就是這樣：
peopleCount depId deptName
25 1 人力資源部
42 2 市場營銷部
一、分組的失敗
首先說說分組的概念。根據關系數據庫理論，分組的概念是（G，·，e ），其中G 是聚集，·是二目運算，e是G的一個成員，SELECT和GROUP BY的關系如下：
（一）當使用聚集函數（例如count），對於SELECT 列表中的項，如果沒有把它當做聚集 函數的參數使用，必須是分組的一部分，例如有一個SQL語句：
SELECT depId
,count(*) as peopleCount
FROM Employee
那就必須在GROUP BY中出現deptId：
SELECT depId
,count(*) as peopleCount
FROM Employee
GROUP BY depId
但是窗口函數是例外的，不必（也不能）出現在Group BY子句中。
而對於可能更改分組（或者聚集函數返回值，例如新的列），則一定要包含在GROUP BY子 句之中。否則就會報錯。
二、窗口函數
知道了分組的基本概念之後，理解窗口函數就容易了，與聚集函數一樣，窗口函數也是針 對元組（就是行）進行聚集，但是不像聚集函數那樣只返回一個值（也就是聚集所有行，然 後計算），窗口函數可以為每個分組返回多個值。執行聚集的元組（行組）是窗口。
例如第一個代碼：select count(*) as cnt from Employee 這很容易，只返回一行，但 是往往需要從不表示聚集或者其他聚集的行中訪問這種聚集數據，窗口函數就解決了這個問 題。例如下面的SQL語句表示用窗口函數從細節行訪問聚集數據，就是員工總數：
SELECT EnglishName
,deptId
,count(*) over() as peopleCount
FROM Employee
ORDER BY 2
OVER關鍵字表明，把Count當成窗口函數，對於查詢返回的每一行，它返回了表中所有行 的計數，括號表示還可以接收一些條件來限定行數，即多一層聚集。
三、partition的使用
partition就可以成為那個括號中的條件，它能夠定義行的分區或者分組，以完成聚集。 空的括號表示分區是整個結果集。partition by是一個移動的GROUP BY，例如：
SELECT EnglishName
,depId
count(*) over(partition by deptId) as peopleCount
FROM Employee
ORDER BY 2
通過partition by depId，為每個部門執行count同一個部門的每個count值相同。所以會 返回很多相同的行，這時可以通過內聯視圖的方式進行解決：
SELECT DISTINCT EnglishName
,depId
,peopleCount
FROM
(SELECT EnglishName
,depId
,count(*) over(partition by depId) as peopleCount
FROM Employee
ORDER BY 2
) x
如果要在Access中使用，由於Access不支持窗口函數，只能使用標量子查詢，代碼如下：
SELECT DISTINCT EnglishName
,depId
,peopleCount
FROM
(SELECT e.EnglishName
,e.depId
,(select count(*) from emp d where e.depId=d.depId) as peopleCount
FROM Employee e,Department d
ORDER BY 2
) x
需要指出的是，窗口函數經過了數據庫專門的優化，所以性能較為優異，比標量子查詢要 好，所以應當盡量使用。
