SQL Server - 開窗函數(shù)

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

SELECT?*?FROM?dbo.Student

S#?????????Sname??????Sage????????????????????Ssex
----------?----------?-----------------------?----------
01?????????趙雷?????????1990-01-01?00:00:00.000?男
02?????????錢電?????????1990-12-21?00:00:00.000?男
03?????????孫風?????????1990-05-20?00:00:00.000?男
04?????????李云?????????1990-08-06?00:00:00.000?男
05?????????周梅?????????1991-12-01?00:00:00.000?女
06?????????吳蘭?????????1992-03-01?00:00:00.000?女
07?????????鄭竹?????????1989-07-01?00:00:00.000?女
08?????????王菊?????????1990-01-20?00:00:00.000?女

--?Over函數(shù),添加一個字段顯示最大年齡
SELECT?*,MAX(DATEDIFF(yyyy,Sage,GETDATE()))OVER()?MaxSage?FROM?dbo.Student

S#?????????Sname??????Sage????????????????????Ssex???????MaxSage
----------?----------?-----------------------?----------?-----------
01?????????趙雷?????????1990-01-01?00:00:00.000?男??????????28
02?????????錢電?????????1990-12-21?00:00:00.000?男??????????28
03?????????孫風?????????1990-05-20?00:00:00.000?男??????????28
04?????????李云?????????1990-08-06?00:00:00.000?男??????????28
05?????????周梅?????????1991-12-01?00:00:00.000?女??????????28
06?????????吳蘭?????????1992-03-01?00:00:00.000?女??????????28
07?????????鄭竹?????????1989-07-01?00:00:00.000?女??????????28
08?????????王菊?????????1990-01-20?00:00:00.000?女??????????28
?
--?Over函數(shù),添加一個字段顯示總人數(shù)
SELECT?*,COUNT(S#)OVER()?總人數(shù)?FROM?dbo.Student

S#?????????Sname??????Sage????????????????????Ssex???????總人數(shù)
----------?----------?-----------------------?----------?-----------
01?????????趙雷?????????1990-01-01?00:00:00.000?男??????????8
02?????????錢電?????????1990-12-21?00:00:00.000?男??????????8
03?????????孫風?????????1990-05-20?00:00:00.000?男??????????8
04?????????李云?????????1990-08-06?00:00:00.000?男??????????8
05?????????周梅?????????1991-12-01?00:00:00.000?女??????????8
06?????????吳蘭?????????1992-03-01?00:00:00.000?女??????????8
07?????????鄭竹?????????1989-07-01?00:00:00.000?女??????????8
08?????????王菊?????????1990-01-20?00:00:00.000?女??????????8
?
--?Partition?By?分組統(tǒng)計數(shù)量
--?根據(jù)性別分組后，統(tǒng)計
SELECT?*,COUNT(*)?OVER(PARTITION?BY?Ssex)?總數(shù)?FROM?dbo.Student

S#?????????Sname??????Sage????????????????????Ssex???????總數(shù)
----------?----------?-----------------------?----------?-----------
01?????????趙雷?????????1990-01-01?00:00:00.000?男??????????4
02?????????錢電?????????1990-12-21?00:00:00.000?男??????????4
03?????????孫風?????????1990-05-20?00:00:00.000?男??????????4
04?????????李云?????????1990-08-06?00:00:00.000?男??????????4
05?????????周梅?????????1991-12-01?00:00:00.000?女??????????4
06?????????吳蘭?????????1992-03-01?00:00:00.000?女??????????4
07?????????鄭竹?????????1989-07-01?00:00:00.000?女??????????4
08?????????王菊?????????1990-01-20?00:00:00.000?女??????????4
?
--?根據(jù)性別分組后，統(tǒng)計、排序
SELECT?*,COUNT(*)?OVER(PARTITION?BY?Ssex?ORDER?BY?Sname)?序號?FROM?dbo.Student

S#?????????Sname??????Sage????????????????????Ssex???????序號
----------?----------?-----------------------?----------?-----------
04?????????李云?????????1990-08-06?00:00:00.000?男??????????1
02?????????錢電?????????1990-12-21?00:00:00.000?男??????????2
03?????????孫風?????????1990-05-20?00:00:00.000?男??????????3
01?????????趙雷?????????1990-01-01?00:00:00.000?男??????????4
08?????????王菊?????????1990-01-20?00:00:00.000?女??????????1
06?????????吳蘭?????????1992-03-01?00:00:00.000?女??????????2
07?????????鄭竹?????????1989-07-01?00:00:00.000?女??????????3
05?????????周梅?????????1991-12-01?00:00:00.000?女??????????4
?
--?Over函數(shù),添加一個字段顯示平均年齡
SELECT?*,AVG(DATEDIFF(yyyy,Sage,GETDATE()))OVER()?平均年齡?FROM?dbo.Student

S#?????????Sname??????Sage????????????????????Ssex???????平均年齡
----------?----------?-----------------------?----------?-----------
01?????????趙雷?????????1990-01-01?00:00:00.000?男??????????26
02?????????錢電?????????1990-12-21?00:00:00.000?男??????????26
03?????????孫風?????????1990-05-20?00:00:00.000?男??????????26
04?????????李云?????????1990-08-06?00:00:00.000?男??????????26
05?????????周梅?????????1991-12-01?00:00:00.000?女??????????26
06?????????吳蘭?????????1992-03-01?00:00:00.000?女??????????26
07?????????鄭竹?????????1989-07-01?00:00:00.000?女??????????26
08?????????王菊?????????1990-01-20?00:00:00.000?女??????????26
?
--Row_Rumber()
SELECT?*,ROW_NUMBER()OVER(ORDER?BY?S#?DESC)?序號?FROM?dbo.Student

S#?????????Sname??????Sage????????????????????Ssex???????序號
----------?----------?-----------------------?----------?--------------------
08?????????王菊?????????1990-01-20?00:00:00.000?女??????????1
07?????????鄭竹?????????1989-07-01?00:00:00.000?女??????????2
06?????????吳蘭?????????1992-03-01?00:00:00.000?女??????????3
05?????????周梅?????????1991-12-01?00:00:00.000?女??????????4
04?????????李云?????????1990-08-06?00:00:00.000?男??????????5
03?????????孫風?????????1990-05-20?00:00:00.000?男??????????6
02?????????錢電?????????1990-12-21?00:00:00.000?男??????????7
01?????????趙雷?????????1990-01-01?00:00:00.000?男??????????8
?
--Row_Rumber()?實現(xiàn)分頁效果
WITH?T?AS?(?
????SELECT?ROW_NUMBER()?OVER?(?ORDER?BY?S#?DESC?)?RowNumber?,*
????FROM?dbo.Student
)
SELECT?*?FROM?T?WHERE?T.RowNumber?BETWEEN?1?AND?3

RowNumber????????????S#?????????Sname??????Sage????????????????????Ssex
--------------------?----------?----------?-----------------------?----------
1????????????????????08?????????王菊?????????1990-01-20?00:00:00.000?女
2????????????????????07?????????鄭竹?????????1989-07-01?00:00:00.000?女
3????????????????????06?????????吳蘭?????????1992-03-01?00:00:00.000?女
?
--Rank()?排名函數(shù),名次相同，跳過
SELECT?*,RANK()OVER(ORDER?BY?Ssex)?名次?FROM?dbo.Student
S#?????????Sname??????Sage????????????????????Ssex???????名次
----------?----------?-----------------------?----------?--------------------
01?????????趙雷?????????1990-01-01?00:00:00.000?男??????????1
02?????????錢電?????????1990-12-21?00:00:00.000?男??????????1
03?????????孫風?????????1990-05-20?00:00:00.000?男??????????1
04?????????李云?????????1990-08-06?00:00:00.000?男??????????1
05?????????周梅?????????1991-12-01?00:00:00.000?女??????????5
06?????????吳蘭?????????1992-03-01?00:00:00.000?女??????????5
07?????????鄭竹?????????1989-07-01?00:00:00.000?女??????????5
08?????????王菊?????????1990-01-20?00:00:00.000?女??????????5
?
--DENSE_Rank()?排名函數(shù)，名次相同不跳過
SELECT?*,DENSE_RANK()OVER(ORDER?BY?Ssex)?名次?FROM?dbo.Student

S#?????????Sname??????Sage????????????????????Ssex???????名次
----------?----------?-----------------------?----------?--------------------
01?????????趙雷?????????1990-01-01?00:00:00.000?男??????????1
02?????????錢電?????????1990-12-21?00:00:00.000?男??????????1
03?????????孫風?????????1990-05-20?00:00:00.000?男??????????1
04?????????李云?????????1990-08-06?00:00:00.000?男??????????1
05?????????周梅?????????1991-12-01?00:00:00.000?女??????????2
06?????????吳蘭?????????1992-03-01?00:00:00.000?女??????????2
07?????????鄭竹?????????1989-07-01?00:00:00.000?女??????????2
08?????????王菊?????????1990-01-20?00:00:00.000?女??????????2
?
--?NTILE()函數(shù)，參數(shù)：記錄總數(shù)/劃分區(qū)域?=?每個區(qū)域數(shù)組，把記錄序號放進數(shù)組?(平均分組)
SELECT?*,NTILE(3)OVER(ORDER?BY?Ssex)?區(qū)域?FROM?dbo.Student

S#?????????Sname??????Sage????????????????????Ssex???????區(qū)域
----------?----------?-----------------------?----------?--------------------
01?????????趙雷?????????1990-01-01?00:00:00.000?男??????????1
02?????????錢電?????????1990-12-21?00:00:00.000?男??????????1
03?????????孫風?????????1990-05-20?00:00:00.000?男??????????1
04?????????李云?????????1990-08-06?00:00:00.000?男??????????2
05?????????周梅?????????1991-12-01?00:00:00.000?女??????????2
06?????????吳蘭?????????1992-03-01?00:00:00.000?女??????????2
07?????????鄭竹?????????1989-07-01?00:00:00.000?女??????????3
08?????????王菊?????????1990-01-20?00:00:00.000?女??????????3