SQL Server-開窓関数
69633 ワード
-- ： ( ) 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 , 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 , SELECT *,COUNT(S#)OVER() FROM dbo.Student S# Sname Sage Ssex ---------- ---------- ----------------------- ---------- ----------- 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 -- ， SELECT *,COUNT(*) OVER(PARTITION BY Ssex) FROM dbo.Student S# Sname Sage Ssex ---------- ---------- ----------------------- ---------- ----------- 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 -- ， 、 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 , 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() 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() , ， 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() ， 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() ， ： / = ， ( ) SELECT *,NTILE(3)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 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
転載先:https://www.cnblogs.com/zhaoshujie/p/9594717.html