Select a.* from 表 a join 表 b on a.[Key] = b.[Key] and abs(datediff(day,a.[date],b.[date])) >= 90
create table aa ([key] char(3),[date] datetime) insert aa select '102','2003-1-1' union all select '103','2003-1-2' union all select '104','2003-1-2' union all select '103','2003-2-3' union all select '102','2003-5-10' Select a.* from aa a join aa b on a.[Key] = b.[Key] and abs(datediff(day,a.[date],b.[date])) >= 90 key date ---- ------------------------------------------------------ 102 2003-01-01 00:00:00.000 102 2003-05-10 00:00:00.000(所影响的行数为 2 行)
--考虑有第二次与第三次间隔小于90的情况: select * from a tem where not exists(select 1 from a group by [key] having [key]=tem.[key] and min([date])=tem.[date]) and exists (select 1 from a group by [key] having [key]=tem.[key] and datediff(day,min([date]),tem.[date])<=90)
declare @t table(key1 varchar(3),dt varchar(10)) insert into @t values('102','2003-1-1') insert into @t values('104','2003-1-2') insert into @t values('102','2003-5-10') insert into @t values('103','2003-1-2') select a.*,b.* from @t a,@t b where datediff(day,convert(datetime,a.dt,20),convert(datetime,b.dt,20))>=90 and a.key1=b.key1
join 表 b
on a.[Key] = b.[Key] and abs(datediff(day,a.[date],b.[date])) >= 90
insert aa select
'102','2003-1-1'
union all select
'103','2003-1-2'
union all select
'104','2003-1-2'
union all select
'103','2003-2-3'
union all select
'102','2003-5-10'
Select a.* from aa a
join aa b
on a.[Key] = b.[Key] and abs(datediff(day,a.[date],b.[date])) >= 90
key date
---- ------------------------------------------------------
102 2003-01-01 00:00:00.000
102 2003-05-10 00:00:00.000(所影响的行数为 2 行)
select * from a tem where not exists(select 1 from a group by [key] having [key]=tem.[key] and min([date])=tem.[date]) and exists (select 1 from a group by [key] having [key]=tem.[key] and datediff(day,min([date]),tem.[date])<=90)
insert into @t values('102','2003-1-1')
insert into @t values('104','2003-1-2')
insert into @t values('102','2003-5-10')
insert into @t values('103','2003-1-2')
select a.*,b.* from @t a,@t b where
datediff(day,convert(datetime,a.dt,20),convert(datetime,b.dt,20))>=90 and a.key1=b.key1