SQL Server 2012 - 開窗函數

-- 開窗函數：在結果集的基礎上進一步處理(聚合操作)

-- Over函數,添加一個欄位顯示最大年齡
SELECT  * ,
        MAX(StuAge) OVER ( ) MaxStuAge
FROM    dbo.Student;

-- Over函數,添加一個欄位顯示總人數
SELECT  * ,
        COUNT(StuID) OVER ( ) StuCount
FROM    dbo.Student;

-- Partition By 分組統計數量
-- 根據性別分組後，統計 
SELECT  COUNT(*) OVER ( PARTITION   BY StuSex ) ,
        *
FROM    dbo.Student; 

-- 根據班級分組後，統計、排序 
SELECT  COUNT(*) OVER ( PARTITION   BY Class ORDER BY Height) ,
        *
FROM    dbo.Student; 

-- Over函數,添加一個欄位顯示平均身高
SELECT  * ,
        AVG(Height) OVER ( ) AgeHeight
FROM    dbo.Student;

--Row_Rumber()
SELECT  ROW_NUMBER() OVER ( ORDER BY StuID DESC ) RowNumber ,
        *
FROM    dbo.Student 

--Row_Rumber() 實現分頁效果
;
WITH    T AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY StuID DESC ) RowNumber ,
                        *
               FROM     dbo.Student
             )
    SELECT  *
    FROM    T
    WHERE   T.RowNumber BETWEEN 1 AND 3;

--Rank() 排名函數,名次相同，跳過
SELECT  RANK() OVER ( ORDER BY Height ) ,
        *
FROM    dbo.Student;

--DENSE_Rank() 排名函數，名次相同不跳過
SELECT  DENSE_RANK() OVER ( ORDER BY Height ) ,
        *
FROM    dbo.Student;

-- NTILE()函數，參數：記錄總數/劃分區域 = 每個區域數組，把記錄序號放進數組 (平均分組)
SELECT  NTILE(3) OVER ( ORDER BY StuSex ) ,
        *
FROM    dbo.Student;