代碼如下復制代碼
-- 開窗函數:在結果集的基礎上進一步處理(聚合操作)
-- Over函數,添加一個字段顯示最大年齡
SELECT* ,
MAX(StuAge) OVER ( ) MaxStuAge
FROM dbo.Student;
-- Over函數,添加一個字段顯示總人數
SELECT* ,
COUNT(StuID) OVER ( ) StuCount
FROM dbo.Student;
-- Partition By 分組統計數量
-- 根據性別分組後,統計
SELECTCOUNT(*) OVER ( PARTITION BYStuSex ) ,
*
FROM dbo.Student;
-- 根據班級分組後,統計、排序
SELECTCOUNT(*) OVER ( PARTITION BYClassORDERBYHeight) ,
*
FROM dbo.Student;
-- Over函數,添加一個字段顯示平均身高
SELECT* ,
AVG(Height) OVER ( ) AgeHeight
FROM dbo.Student;
--Row_Rumber()
SELECTROW_NUMBER() OVER (ORDERBYStuIDDESC) RowNumber ,
*
FROM dbo.Student
--Row_Rumber() 實現分頁效果
;
WITH TAS(SELECT ROW_NUMBER() OVER (ORDERBYStuIDDESC) RowNumber ,
*
FROM dbo.Student
)
SELECT*
FROM T
WHERE T.RowNumberBETWEEN1AND3;
--Rank() 排名函數,名次相同,跳過
SELECTRANK() OVER (ORDERBYHeight ) ,
*
FROM dbo.Student;
--DENSE_Rank() 排名函數,名次相同不跳過
SELECTDENSE_RANK() OVER (ORDERBYHeight ) ,
*
FROM dbo.Student;
-- NTILE()函數,參數:記錄總數/劃分區域 = 每個區域數組,把記錄序號放進數組 (平均分組)
SELECTNTILE(3) OVER (ORDERBYStuSex ) ,
*
FROM dbo.Student;