现在有一个用户登录表UserLogin,一个用户ID有多次的登录时间,例如:
userid logintime
101 '2012-08-14 10:00'
101 '2012-08-14 10:20'
101 '2012-08-14 10:30'
101 '2012-08-14 11:00'
102 '2012-08-15 11:30'
102 '2012-08-14 10:15'
...如何最多只保存每个用户的最新的两条登录信息,得到如下的结果:
userid logintime
101 '2012-08-14 10:00'
101 '2012-08-14 10:20'
101 '2012-08-14 10:30'
102 '2012-08-15 11:30'
102 '2012-08-14 10:15'
...
求赐教,不胜感激!
userid logintime
101 '2012-08-14 10:00'
101 '2012-08-14 10:20'
101 '2012-08-14 10:30'
101 '2012-08-14 11:00'
102 '2012-08-15 11:30'
102 '2012-08-14 10:15'
...如何最多只保存每个用户的最新的两条登录信息,得到如下的结果:
userid logintime
101 '2012-08-14 10:00'
101 '2012-08-14 10:20'
101 '2012-08-14 10:30'
102 '2012-08-15 11:30'
102 '2012-08-14 10:15'
...
求赐教,不胜感激!
--是要删除表中多余的数据,保留每个用户最新的两条的数据吗?如:
delete UserLogin from UserLogin a
where (select count(distinct logintime) from userlogin where userid=a.userid and logintime>=a.logintime)>2--如果是查询最新的2条,不改变原始数据
select * from UserLogin a
where (select count(distinct logintime) from userlogin where userid=a.userid and logintime>=a.logintime)<=2
delete UserLogin from UserLogin a
where a.userid=(select userid from inserted) and logintime not in (select top 2 logintime from userlogin where userid=(select userid from inserted) order by logintime)
; with cte ( select row_number() over( paritition by userid order by userid, logintime desc ) as xh ,* from UserLogin ) delete from cte where xh >=3