ROW_NUMBER() OVER 用处

ROW_NUMBER() OVER 

1，分组排序

2，删除重复行

CREATE TABLE album
(
  Id int identity (1,1),
  song_name varchar(1000),
  singer varchar(100),
  Insert_DT datetime
)
INSERT album VALUES ('song1','singer1', GETDATE()-1)
INSERT album VALUES ('song2','singer2', GETDATE()-2)
INSERT album VALUES ('song2','singer2', GETDATE()-3)
INSERT album VALUES ('song4','singer4', GETDATE()-4)
INSERT album VALUES ('song4','singer4', GETDATE()-5)
INSERT album VALUES ('song4','singer4', GETDATE()-6)
INSERT album VALUES ('song3','singer3', GETDATE()-7)

select * from album

select Rnum=row_number() over (partition by song_name, singer order by Insert_dt),*
from album

 

with [cte duplicate] as
(
select Rnum=row_number() over (partition by song_name, singer order by Insert_dt),*
from album
)
delete from [cte duplicate] where Rnum > 1

select * from album
--drop table album