create table tab(a int,b varchar(10),c datetime ) insert tab select 1 ,'cat' ,'2006/4/11' union select 2 ,'bee' ,'2006/5/7' union select 3 ,'dea' ,'2006/7/22' union select 4 ,'kle' ,'2007/3/1' union select 5 ,'soe' ,'2006/4/21' union select 6 ,'que' ,'2005/11/7' union select 7 ,'jkc' ,'2007/2/27' union select 8 ,'ore' ,'2007/6/12'gocreate proc p_delete @month int as delete tab where month(c) = @monthgo exec p_delete 4goselect * from tabdrop table tab drop proc p_delete/* a b c ----------- ---------- ------------------------------------------------------ 2 bee 2006-05-07 00:00:00.000 3 dea 2006-07-22 00:00:00.000 4 kle 2007-03-01 00:00:00.000 6 que 2005-11-07 00:00:00.000 7 jkc 2007-02-27 00:00:00.000 8 ore 2007-06-12 00:00:00.000(6 row(s) affected) */
declare @N as int declare @mindate as datetimeset @N = 3 select @mindate = min(c) from Adelete from a where DATEDIFF ( month, @mindate , c) <= @N
create proc pro_a @n int as declare @a datetime select @a=min(c) from 表A delete 表A where datediff(mm,@a,c)<@n
declare @n int declare @c datetime decalre @ec datetime select top 1 @c = c from A order by c set @ec = dateadd(mm,@n,@c)delete A from (select * from A where year(c)<= year(@ec) and month(c)<= month(@ec)) as B where B.a = A.a
谢谢大家。 libin_ftsafe(子陌红尘:TS for Banking Card)、dawugui(潇洒老乌龟)、wgzaaa() 三人所采用的方法是一样的。 但是,如果 c 列的时间里,月份出现中断就无法满足要求了。 譬如 c ---------- 2006-5-3 2006-8-1 2006-9-5第一行是5月份,第二行是8月份,用 DateDiff(mm,startDate,EndDate)就无法满足删除最早两个月,也就是5、8月的数据。
create table #( rq datetime)insert # select '2006-5-3' union all select '2006-8-1' union all select '2006-9-5'declare @N int --最早N个月 declare @rq datetimeselect @N=2 --2个月select @rq = rq from # t1 where (select count(*) from # t2 where t1.rq>=t2.rq)=@Nselect * from # delete from # where rq <= @rq select * from #drop table # 结果 删除前 2006-05-03 00:00:00.000 2006-08-01 00:00:00.000 2006-09-05 00:00:00.000 删除后 2006-09-05 00:00:00.000
create table tab(a int,b varchar(10),c datetime )
insert tab
select 1 ,'cat' ,'2006/4/11'
union select 2 ,'bee' ,'2006/5/7'
union select 3 ,'dea' ,'2006/7/22'
union select 4 ,'kle' ,'2007/3/1'
union select 5 ,'soe' ,'2006/4/21'
union select 6 ,'que' ,'2005/11/7'
union select 7 ,'jkc' ,'2007/2/27'
union select 8 ,'ore' ,'2007/6/12'gocreate proc p_delete @month int
as
delete tab where month(c) = @monthgo exec p_delete 4goselect * from tabdrop table tab
drop proc p_delete/*
a b c
----------- ---------- ------------------------------------------------------
2 bee 2006-05-07 00:00:00.000
3 dea 2006-07-22 00:00:00.000
4 kle 2007-03-01 00:00:00.000
6 que 2005-11-07 00:00:00.000
7 jkc 2007-02-27 00:00:00.000
8 ore 2007-06-12 00:00:00.000(6 row(s) affected)
*/
declare @mindate as datetimeset @N = 3
select @mindate = min(c) from Adelete from a where DATEDIFF ( month, @mindate , c) <= @N
declare @a datetime
select @a=min(c) from 表A
delete 表A where datediff(mm,@a,c)<@n
declare @c datetime
decalre @ec datetime
select top 1 @c = c from A order by c
set @ec = dateadd(mm,@n,@c)delete A from (select * from A where year(c)<= year(@ec) and month(c)<= month(@ec)) as B where B.a = A.a
libin_ftsafe(子陌红尘:TS for Banking Card)、dawugui(潇洒老乌龟)、wgzaaa() 三人所采用的方法是一样的。
但是,如果 c 列的时间里,月份出现中断就无法满足要求了。
譬如
c
----------
2006-5-3
2006-8-1
2006-9-5第一行是5月份,第二行是8月份,用 DateDiff(mm,startDate,EndDate)就无法满足删除最早两个月,也就是5、8月的数据。
http://community.csdn.net/Expert/TopicView3.asp?id=5709950
rq datetime)insert #
select '2006-5-3'
union all
select '2006-8-1'
union all
select '2006-9-5'declare @N int --最早N个月
declare @rq datetimeselect @N=2 --2个月select @rq = rq from # t1 where (select count(*) from # t2 where t1.rq>=t2.rq)=@Nselect * from #
delete from # where rq <= @rq
select * from #drop table #
结果
删除前
2006-05-03 00:00:00.000
2006-08-01 00:00:00.000
2006-09-05 00:00:00.000
删除后
2006-09-05 00:00:00.000