if object_id('tb') is not null drop table tb
go
create table tb(name char(10), time datetime)
go
insert into tb select 'A' , getdate()
union all select 'A' , dateadd(day , 1 , getdate())
union all select 'B' , dateadd(day , -1 , getdate())
union all select 'A' , dateadd(day , 2 , getdate())
union all select 'A' , dateadd(day , 5 , getdate())
union all select 'B' , dateadd(day , 1 , getdate())select * from tb
name time
A 2010-11-12 11:49:53.940
A 2010-11-13 11:49:53.940
B 2010-11-11 11:49:53.940
A 2010-11-14 11:49:53.940
A 2010-11-17 11:49:53.940
B 2010-11-13 11:49:53.940
要求得到结果
name time
A 2010-11-12 11:49:53.940
A 2010-11-13 11:49:53.940
A 2010-11-14 11:49:53.940谢谢!
go
create table tb(name char(10), time datetime)
go
insert into tb select 'A' , getdate()
union all select 'A' , dateadd(day , 1 , getdate())
union all select 'B' , dateadd(day , -1 , getdate())
union all select 'A' , dateadd(day , 2 , getdate())
union all select 'A' , dateadd(day , 5 , getdate())
union all select 'B' , dateadd(day , 1 , getdate())select * from tb
name time
A 2010-11-12 11:49:53.940
A 2010-11-13 11:49:53.940
B 2010-11-11 11:49:53.940
A 2010-11-14 11:49:53.940
A 2010-11-17 11:49:53.940
B 2010-11-13 11:49:53.940
要求得到结果
name time
A 2010-11-12 11:49:53.940
A 2010-11-13 11:49:53.940
A 2010-11-14 11:49:53.940谢谢!
select name from tb
Group By name
Having Count(*)>3
)
go
create table tb(name char(10), time datetime)
go
insert into tb select 'A' , getdate()
union all select 'A' , dateadd(day , 1 , getdate())
union all select 'B' , dateadd(day , -1 , getdate())
union all select 'A' , dateadd(day , 2 , getdate())
union all select 'A' , dateadd(day , 5 , getdate())
union all select 'B' , dateadd(day , 1 , getdate())select * from tb where name = 'A'
go
create table tb(name char(10), time datetime)
go
insert into tb select 'A' , getdate()
union all select 'A' , dateadd(day , 1 , getdate())
union all select 'B' , dateadd(day , -1 , getdate())
union all select 'A' , dateadd(day , 2 , getdate())
union all select 'A' , dateadd(day , 5 , getdate())
union all select 'B' , dateadd(day , 1 , getdate());with cte as
(
select a.*,b.name as name1,b.time as time1 from tb a
left join tb b
on datediff(day,a.time,b.time)=1 and a.name=b.name
where b.name is not null
)
select name,time from cte
union
select name1,time1 from cte name time
---------- -----------------------
A 2010-11-12 12:06:56.673
A 2010-11-13 12:06:56.673
A 2010-11-14 12:06:56.673(3 行受影响)
into #t
from tb a
left join tb b
on datediff(day,a.time,b.time)=1 and a.name=b.name
where b.name is not null
select name,time from #t
union
select name1,time1 from #t
(
select a.*,name1=b.name,time1=b.time,name2=c.name,time2=c.time from tb a
join tb b on a.name = b.name and datediff(day,a.time,b.time) = 1
join tb c on b.name = c.name and datediff(day,b.time,c.time) = 1
)
select name,time from cte
union
select name1,time1 from cte
union
select name2,time2 from cte
(
select name,time,row_number() over(partition by name order by time) rowid,
datediff(day,'2010-1-1',time) dd
from tb
)select a.name,a.time
from cte a
join (select name,dd-rowid groupid from cte group by name,dd-rowid having count(*) >= 3) b --这里的3可以换成其它数字
on a.name = b.name and a.dd-rowid = b.groupid
先求出连续三天中的中间那天的记录
select 日期 from
(select CONVERT(varchar(100), [time], 112) as 日期 from tb
union
select CONVERT(varchar(100), dateadd(d,-1,[time]), 112) as 日期 from tb
union
select CONVERT(varchar(100), dateadd(d,1,[time]), 112) as 日期 from tb
) group by 日期 having count(*)>=3然后再根据“日期”得出那些连续的记录
不好意思,少些了个AS
select 日期 from
(...) AS T_tmep group by 日期 having count(*)>=3