Sql 裡面 ROW_NUMBER 應用情境

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) ASRowNum FROM [dbo].[UserEnroll])DELETE FROM  UserEnrollWithRowNumberWHERE 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