先要判断一下数据库中的记录数是不是超过5条,大于则执行以下语句,否则不要执行
delete from 表 where lastupdateDate < (
select top 1 lastupdateDate
from
(select top 5 lastupdateDate as rowcount from 表 order by lastupdateDate desc) a
order by lastupdateDate asc)
delete from 表 where lastupdateDate < (
select top 1 lastupdateDate
from
(select top 5 lastupdateDate as rowcount from 表 order by lastupdateDate desc) a
order by lastupdateDate asc)
where (select count(*) from t a where a.userId=t.userId
and a.astUpdateDate>=t.astUpdateDate)>5
where a.objId not in
(select top 5 objId from 表 where userId = a.userId order by LastUpdateDATE desc)
delete from t
where astUpdateDate not in(select top 5 astUpdateDate from t a
where a.userId=t.userId order by a.astUpdateDate desc)
where a.objId
not in (select top 5 objId from tablename
where userId = a.userId
order by LastUpdateDATE desc)
select top5 cast(userid as varchar(30))+cast(objid as varchar(30))+cast(lastupdatedate as varchar(30)) from 表 order by lastupdatddate DESC)
select @num=count(*) from table
delete from table A where lastupdateDate exist(
select top @num - 5 lastupdateDate from table B where A.userid=B.userid order by lastupdateDate )
select 1 from (
select * from #t1 a where (select count(1) from #t1 where userid=a.userid
and lastUpdateDate>a.lastUpdateDate)
<5) b where userid=#t1.userid and oblID=#t1.oblID and lastUpdateDate=#t1.lastUPdateDate
)