ROW_NUMBER 返回按一定规则排序的当前记录对应的行号
比如我们有这样一个应用场景:
现在有个比赛,需要从网上参赛者从从网络上报名,然后去最早报名的5个人参加比赛,为此我们实现如下:
1.为此我们要建立一张表来保存报名参赛者的姓名及起报名时间
CREATE
TABLE [dbo].[UserEnroll](
[UserName] [nvarchar] (50) NULL, --参赛者的姓名
[EnrollTime] [datetime] NULL --报名时间
)
ON [PRIMARY]
2.我们Sql 向表中插入数据,模拟参赛者报名
insert into [dbo].[UserEnroll] values('CC', GETDATE()) insert into [dbo].[UserEnroll] values('CC1', DateAdd(DAY,-1,GETDATE())) insert into [dbo].[UserEnroll] values('CC2', DateAdd(DAY,-2,GETDATE())) insert into [dbo].[UserEnroll] values('CC3', DateAdd(DAY,-3,GETDATE())) insert into [dbo].[UserEnroll] values('CC4', DateAdd(DAY,-4,GETDATE ())) insert into [dbo].[UserEnroll] values('CC5', DateAdd(DAY,-5,GETDATE())) insert into [dbo].[UserEnroll] values('CC6', DateAdd(DAY,-6,GETDATE())) insert into [dbo].[UserEnroll] values('CC7', DateAdd(DAY,-7,GETDATE()))
3.删除非最早5个报名的人
a. 给表加上行号
SELECT *, ROW_NUMBER() OVER(ORDER BY EnrollTime) AS RowNum FROM [dbo].[UserEnroll]
结果如下:UserName EnrollTime RowNum
CC7 2010-05-11 17:38:42.403 1
CC6 2010-05-12 17:38:42.403 2
CC5 2010-05-13 17:38:42.403 3
CC4 2010-05-14 17:38:42.403 4
CC3 2010-05-15 17:38:42.403 5
CC2 2010-05-16 17:38:42.403 6
CC1 2010-05-17 17:38:42.403 7
CC 2010-05-18 17:38:42.403 8
b. 那么我们删除RowNum 大于5的记录
WITH UserEnrollWithRowNumber AS ( SELECT *, ROW_NUMBER() OVER(ORDER BY EnrollTime) AS RowNum FROM [dbo].[UserEnroll]) DELETE FROM UserEnrollWithRowNumber WHERE RowNum > 5
结果为 effect 3 rows
c. 再用a步中的语句查询报名表结果为
UserName EnrollTime RowNum
CC7 2010-05-11 17:38:42.403 1
CC6 2010-05-12 17:38:42.403 2
CC5 2010-05-13 17:38:42.403 3
CC4 2010-05-14 17:38:42.403 4
CC3 2010-05-15 17:38:42.403 5