假设我的表结构是这样的:
T_UserLogin(Id,UserId,UserName,LoginTime)//LoginTime:登录时间这个表代表所有用户的登录信息,我现在想要把该表中所有用户的登录信息只保留第一天和最后一天的数据,其他的全部删除,我该怎么写呢?谢谢
T_UserLogin(Id,UserId,UserName,LoginTime)//LoginTime:登录时间这个表代表所有用户的登录信息,我现在想要把该表中所有用户的登录信息只保留第一天和最后一天的数据,其他的全部删除,我该怎么写呢?谢谢
(
select top 1 * from T_UserLogin order by LoginTime
union all
select top 1 * from T_UserLogin order by LoginTime desc
)delete t from T_UserLogin t where not exists(select 1 from f where LoginTime=t.LoginTime)
(
select top 1 Id from T_UserLogin tb where ta.UserId=tb.UserId order by LoginTime
union all
select top 1 Id from T_UserLogin tb where ta.UserId=tb.UserId order by LoginTime desc
)
FROM
T_UserLogin a
WHERE NOT exists(SELECT 1 FROM T_UserLogin WHERE UserName=a.UserName HAVING a.LoginTime>(MIN(LoginTime) AND a.LoginTime<MAX(LoginTime)) )
DELETE T_UserLogin WHERE ID NOT IN (
select ID FROM T_UserLogin TA WHERE NOT EXISTS (SELECT 1 FROM T_UserLogin TB WHERE TA.userid=TB.userid AND logintime<TA.logintime)
UNION ALL
select ID FROM T_UserLogin TA WHERE NOT EXISTS (SELECT 1 FROM T_UserLogin TB WHERE TA.userid=TB.userid AND logintime>TA.logintime)
)