SQL Server - 开窗函数

-- 开窗函数：在结果集的基础上进一步处理(聚合操作) 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