SQL Server - 開窗函數(shù)
[導(dǎo)讀]--?開窗函數(shù)：在結(jié)果集的基礎(chǔ)上進(jìn)一步處理(聚合操作)
SELECT?*?FROM?dbo.Student
S#?????????Sname??????Sage??????????????????
--?開窗函數(shù)：在結(jié)果集的基礎(chǔ)上進(jìn)一步處理(聚合操作) 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?????????孫風(fēng)?????????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ù),添加一個(gè)字段顯示最大年齡 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?????????孫風(fēng)?????????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ù),添加一個(gè)字段顯示總?cè)藬?shù) SELECT?*,COUNT(S#)OVER()?總?cè)藬?shù)?FROM?dbo.Student S#?????????Sname??????Sage????????????????????Ssex???????總?cè)藬?shù) ----------?----------?-----------------------?----------?----------- 01?????????趙雷?????????1990-01-01?00:00:00.000?男??????????8 02?????????錢電?????????1990-12-21?00:00:00.000?男??????????8 03?????????孫風(fēng)?????????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)計(jì)數(shù)量 --?根據(jù)性別分組后，統(tǒng)計(jì) 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?????????孫風(fēng)?????????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)計(jì)、排序 SELECT?*,COUNT(*)?OVER(PARTITION?BY?Ssex?ORDER?BY?Sname)?序號(hào)?FROM?dbo.Student S#?????????Sname??????Sage????????????????????Ssex???????序號(hào) ----------?----------?-----------------------?----------?----------- 04?????????李云?????????1990-08-06?00:00:00.000?男??????????1 02?????????錢電?????????1990-12-21?00:00:00.000?男??????????2 03?????????孫風(fēng)?????????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ù),添加一個(gè)字段顯示平均年齡 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?????????孫風(fēng)?????????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)?序號(hào)?FROM?dbo.Student S#?????????Sname??????Sage????????????????????Ssex???????序號(hào) ----------?----------?-----------------------?----------?-------------------- 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?????????孫風(fēng)?????????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()?實(shí)現(xiàn)分頁(yè)效果 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ù),名次相同，跳過(guò) 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?????????孫風(fēng)?????????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ù)，名次相同不跳過(guò) 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?????????孫風(fēng)?????????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ū)域?=?每個(gè)區(qū)域數(shù)組，把記錄序號(hào)放進(jìn)數(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?????????孫風(fēng)?????????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
下載文檔
