帮忙写一个SQL语句(SQL2005存储过程中),详情如下:userID Lastlogintime
01 22
01 05
01 13
02 15
02 15需要将表中同一用户只保留最后登陆时间最靠后的数据,其他的删除,使其变为
userID Lastlogintime
01 22
02 15求实现此功能的语句!!!!
01 22
01 05
01 13
02 15
02 15需要将表中同一用户只保留最后登陆时间最靠后的数据,其他的删除,使其变为
userID Lastlogintime
01 22
02 15求实现此功能的语句!!!!
go
--> -->
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([userID] nvarchar(2),[Lastlogintime] nvarchar(2))
Insert #A
select N'01',N'22' union all
select N'01',N'05' union all
select N'01',N'13' union all
select N'02',N'15' union all
select N'02',N'15'
Go
DELETE a
FROM (Select *,row=ROW_NUMBER()OVER(PARTITION BY [userID] ORDER BY [Lastlogintime] desc) from #A) AS a WHERE row>1SELECT * FROM #A/*
userID Lastlogintime
01 22
02 15
*/
go
--> -->
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([userID] nvarchar(2),[Lastlogintime] nvarchar(2))
Insert #A
select N'01',N'22' union all
select N'01',N'05' union all
select N'01',N'13' union all
select N'02',N'15' union all
select N'02',N'15'
Go
select distinct userID ,Lastlogintime INTO # from #A as a where not exists(select 1 from #A where userid=a.userId and Lastlogintime>a.Lastlogintime)TRUNCATE TABLE #AINSERT #A SELECT * FROM #SELECT * FROM #A
(
select px=row_number()over(parition by userid order by Lastlogintime desc),* from tb
)delete f from f t where exists(select 1 from tb where userID=t.userID and px<t.px)
delete t from tb t where exists(
select 1 from tb where userID=t.userID and Lastlogintime<t.Lastlogintime
)