create proc dele_data
@username varchar(50)
as
declare @NUM int
declare @PKID int
select @NUM=count(ID) from log where logoname=@username
if @NUM>3
begin
WHILE @NUM<4
begin
select @PKID=id from logo where id=@username order by logotime desc
delete from Log where id=@PKID
end
end
@username varchar(50)
as
declare @NUM int
declare @PKID int
select @NUM=count(ID) from log where logoname=@username
if @NUM>3
begin
WHILE @NUM<4
begin
select @PKID=id from logo where id=@username order by logotime desc
delete from Log where id=@PKID
end
end
group by LOGNAME having count(*)>3select id into #Temp2 from log
where not exists (
select * from #Temp1
whete #Temp1.LOGNAME=#Temp2.LOGNAME
)insert #Temp2
select top 3 id from log a,#Temp1 t
where a.LOGNAME=t.LOGNAMEdelete log
where id not in (
select id from #Temp2
)drop table #Temp1,#Temp2
select distinct logname from log
for read onlyopen cursorfetch from lognamecursor into @logname
while @@fetch_status=0
begin
select distinct logname from log
for read onlyopen cursorfetch from lognamecursor into @logname
while @@fetch_status=0
begin
delete from log where id not in (select top 3 id from log where logname=@logname order by id desc)
fetch next from lognamecursor into @logname
end close lognamecursordeallocate lognamecursor
YourTable tmpA,
(select LOGNAME,Min(LOGTIME) as LogTime from YourTable t1
where t1.ID IN (select top 3 t2.iD from YourTable t2 where t2.LOGNAME = t1.LOGNAME order by LOGTIME desc)
GROUP BY LogNAME HAVING COUNT(*)=3
) as TmpB
WHERE tmpA.LogName=tmpB.LogName AND tmpA.LogTime<tmpB.LogTime
delete log from log t1,
--选择出需要删除的人的名
(select LogName from log group by LogName having count(*)>3 ) t2 where t1.LogName=t2.LogName --以下排除不要删除的id
and t1.id not
in (select top 3 a.Id from log a,
(select LogName from log group by LogName having
count(*)>3 ) b where a.LogName=b.LogName order by a.logTime desc)) t3
本来我写了个过程,用游标了
select [log].id From [log],( select [id],Count(*) as cnt
From
(
SELECT a1.[id]
FROM [Log] as a1
INNER JOIN [Log] as a2 ON a1.[ID] <= a2.[ID] AND a1.LogName = a2.LogName
) as a3
group by ID
) as a4
where a4.[id]=[log].[id] and a4.cnt>3)