-- 原表数据 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 女 -- MAX(),最大年龄 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 -- COUNT(),总人数 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 -- AVG(),平均年龄 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_NUMBER(),计数 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 -- FIRST_VALUE() 第一个 select *,first_value(sname)over(partition by Ssex order by S) [First Value] from Student S Sname Sage Ssex First Value -- ----- ------------------ ---- ---------- 01 赵雷 1990/1/1 0:00:00 男 赵雷 02 钱电 1990/12/21 0:00:00 男 赵雷 03 孙风 1990/5/20 0:00:00 男 赵雷 04 李云 1990/8/6 0:00:00 男 赵雷 09 宋江 1971/3/15 15:56:30 男 赵雷 05 周梅 1991/12/1 0:00:00 女 周梅 06 吴兰 1992/3/1 0:00:00 女 周梅 07 郑竹 1989/7/1 0:00:00 女 周梅 08 王菊 1990/1/20 0:00:00 女 周梅 -- LAST_VALUE() 最后一个 select *, last_value(S)over(partition by Ssex order by S) [LAST-1], last_value(S)over(partition by Ssex order by S desc) [LAST-2], last_value(S)over(partition by Ssex order by S ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) [LAST-3] from Student S Sname Sage Ssex LAST-1 LAST-2 LAST-3 -- ----- ------------------ ---- ------ ------ ------ 09 宋江 1971/3/15 15:56:30 男 09 09 09 04 李云 1990/8/6 0:00:00 男 04 04 09 03 孙风 1990/5/20 0:00:00 男 03 03 09 02 钱电 1990/12/21 0:00:00 男 02 02 09 01 赵雷 1990/1/1 0:00:00 男 01 01 09 08 王菊 1990/1/20 0:00:00 女 08 08 08 07 郑竹 1989/7/1 0:00:00 女 07 07 08 06 吴兰 1992/3/1 0:00:00 女 06 06 08 05 周梅 1991/12/1 0:00:00 女 05 05 08 --说明: 上面结果有点怪,可以这样理解LAST_VALUE()默认统计范围是 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- LAG() 上一个 select *,lag(sname)over(partition by Ssex order by S) [上一个] from Student S Sname Sage Ssex 上一个 -- ----- ------------------ ---- ---- 01 赵雷 1990/1/1 0:00:00 男 NULL 02 钱电 1990/12/21 0:00:00 男 赵雷 03 孙风 1990/5/20 0:00:00 男 钱电 04 李云 1990/8/6 0:00:00 男 孙风 09 宋江 1971/3/15 15:56:30 男 李云 05 周梅 1991/12/1 0:00:00 女 NULL 06 吴兰 1992/3/1 0:00:00 女 周梅 07 郑竹 1989/7/1 0:00:00 女 吴兰 08 王菊 1990/1/20 0:00:00 女 郑竹 -- LEAD() 下一个 select *,lead(sname)over(partition by Ssex order by S) [下一个] from Student S Sname Sage Ssex 下一个 -- ----- ------------------ ---- ---- 01 赵雷 1990/1/1 0:00:00 男 钱电 02 钱电 1990/12/21 0:00:00 男 孙风 03 孙风 1990/5/20 0:00:00 男 李云 04 李云 1990/8/6 0:00:00 男 宋江 09 宋江 1971/3/15 15:56:30 男 NULL 05 周梅 1991/12/1 0:00:00 女 吴兰 06 吴兰 1992/3/1 0:00:00 女 郑竹 07 郑竹 1989/7/1 0:00:00 女 王菊 08 王菊 1990/1/20 0:00:00 女 NULL