create table 表a
(Bvisitor varchar(5), [Datetime] Datetime)
insert into a
select 'aaaa','1994-03-01'
union all select 'aaaa','1998-02-01'
union all select 'aaaa','1999-03-01'
union all select 'aaaa','1995-02-01'
union all select 'aaaa','1995-01-02'
union all select 'aaaa','1998-09-01'
union all select 'aaaa','1995-03-01'
union all select 'aaaa','1995-04-02'
union all select 'aaaa','1995-06-01'
union all select 'aaaa','1995-05-02'
union all select 'bbbb','1995-06-01'
union all select 'bbbb','1995-05-02'我现在要写个存储过程,我在.net中定时调用这个存储过程,发现表里面有Bvisitor有相同的数据我只留最进的8条记录,如果没达到8条就不删除
(Bvisitor varchar(5), [Datetime] Datetime)
insert into a
select 'aaaa','1994-03-01'
union all select 'aaaa','1998-02-01'
union all select 'aaaa','1999-03-01'
union all select 'aaaa','1995-02-01'
union all select 'aaaa','1995-01-02'
union all select 'aaaa','1998-09-01'
union all select 'aaaa','1995-03-01'
union all select 'aaaa','1995-04-02'
union all select 'aaaa','1995-06-01'
union all select 'aaaa','1995-05-02'
union all select 'bbbb','1995-06-01'
union all select 'bbbb','1995-05-02'我现在要写个存储过程,我在.net中定时调用这个存储过程,发现表里面有Bvisitor有相同的数据我只留最进的8条记录,如果没达到8条就不删除
(Bvisitor varchar(5), [Datetime] Datetime)
insert into @a
select 'aaaa','1994-03-01'
union all select 'aaaa','1998-02-01'
union all select 'aaaa','1999-03-01'
union all select 'aaaa','1995-02-01'
union all select 'aaaa','1995-01-02'
union all select 'aaaa','1998-09-01'
union all select 'aaaa','1995-03-01'
union all select 'aaaa','1995-04-02'
union all select 'aaaa','1995-06-01'
union all select 'aaaa','1995-05-02'
union all select 'bbbb','1995-06-01'
union all select 'bbbb','1995-05-02'
select top 8 Bvisitor,max(datetime) as [datetime] from @a group by Bvisitor;
insert into gssg
select 'aaaa','1994-03-01'
union all select 'aaaa','1998-02-01'
union all select 'aaaa','1999-03-01'
union all select 'aaaa','1995-02-01'
union all select 'aaaa','1995-01-02'
union all select 'aaaa','1998-09-01'
union all select 'aaaa','1995-03-01'
union all select 'aaaa','1995-04-02'
union all select 'aaaa','1995-06-01'
union all select 'aaaa','1995-05-02'
union all select 'bbbb','1995-06-01'
union all select 'bbbb','1995-05-02'select * from gssg a where [datetime] in(select top 8 [Datetime] from gssg where bvisitor=a.bvisitor)
(Bvisitor varchar(5), [Datetime] Datetime)
insert into #a
select 'aaaa','1994-03-01'
union all select 'aaaa','1998-02-01'
union all select 'aaaa','1999-03-01'
union all select 'aaaa','1995-02-01'
union all select 'aaaa','1995-01-02'
union all select 'aaaa','1998-09-01'
union all select 'aaaa','1995-03-01'
union all select 'aaaa','1995-04-02'
union all select 'aaaa','1995-06-01'
union all select 'aaaa','1995-05-02'
union all select 'bbbb','1995-06-01'
union all select 'bbbb','1995-05-02'delete from #a
where rtrim(Bvisitor) + rtrim(datetime) in
(select rtrim(Bvisitor) + rtrim(datetime) from
(select *, (select count(1) from #a t2 where t2.Datetime >= t1.Datetime and t1.Bvisitor=t2.Bvisitor) [_index]
from #a t1) a where a._index > 8)select * from #a/*
Bvisitor Datetime
-------- ------------------------------------------------------
aaaa 1998-02-01 00:00:00.000
aaaa 1999-03-01 00:00:00.000
aaaa 1995-02-01 00:00:00.000
aaaa 1998-09-01 00:00:00.000
aaaa 1995-03-01 00:00:00.000
aaaa 1995-04-02 00:00:00.000
aaaa 1995-06-01 00:00:00.000
aaaa 1995-05-02 00:00:00.000
bbbb 1995-06-01 00:00:00.000
bbbb 1995-05-02 00:00:00.000(10 row(s) affected)
*/
drop table #a