--创建表
create table tb_test
(
code int,
st_date datetime,
end_date datetime
)
go
--插入数据
insert into tb_test
select 1, '2011/01/01', '2011/01/31' union all
select 1, '2011/02/01', '2011/02/20' union all
select 2, '2011/01/01', '2011/01/31' union all
select 3, '2011/01/01', '2011/01/31' union all
select 3, '2014/01/01', '2014/02/20' --查询
with ta as(
select code,min(st_date) as st_date,max(end_date) as end_date,
datediff(yy,min(st_date),max(end_date)) as diff from tb_test
group by code
)
,tb as
(
select code,st_date,end_date from ta where diff=0
union all
select * from tb_test where datepart(yy,st_date)=2014 and datepart(yy,end_date)=2014
and not exists (select 1 from ta where diff=0 and ta.code=tb_test.code)
)
select * from tb
需求还不是很细,可再细化,这是按楼主的这模糊的需求实现的!
create table tb_test
(
code int,
st_date datetime,
end_date datetime
)
go
--插入数据
insert into tb_test
select 1, '2011/01/01', '2011/01/31' union all
select 1, '2011/02/01', '2011/02/20' union all
select 2, '2011/01/01', '2011/01/31' union all
select 3, '2011/01/01', '2011/01/31' union all
select 3, '2014/01/01', '2014/02/20' --查询
with ta as(
select code,min(st_date) as st_date,max(end_date) as end_date,
datediff(yy,min(st_date),max(end_date)) as diff from tb_test
group by code
)
,tb as
(
select code,st_date,end_date from ta where diff=0
union all
select * from tb_test where datepart(yy,st_date)=2014 and datepart(yy,end_date)=2014
and not exists (select 1 from ta where diff=0 and ta.code=tb_test.code)
)
select * from tb
需求还不是很细,可再细化,这是按楼主的这模糊的需求实现的!
如上 常量'2014' 用 datepart(yy,getdate()) 代替
create table tb_test
(
code int,
st_date datetime,
end_date datetime
)
go
--插入数据
insert into tb_test
select 1, '2011/01/01', '2011/01/31' union all
select 1, '2011/02/01', '2011/02/20' union all
select 1, '2011/02/21', '2011/02/28' union all
select 2, '2011/01/01', '2011/01/31' union all
select 3, '2011/01/01', '2011/01/31' union all
select 3, '2014/01/01', '2014/02/20' union all
select 3, '2014/02/21', '2014/02/28' union all
select 3, '2014/03/01', '2014/03/31' --查询
with ta as --查询同一组code中是否有连续的情况
(
select a.code,count(a.code)+1 as count from tb_test a
join tb_test b
on b.st_date=dateadd(dd,1,a.end_date) and a.code=b.code
group by a.code
)
,tb as --查询同组中是否所有code都连续,比如code3有3条数据连续,但还有1条不连续,排除code3
(
select ta.code from ta
join
(select code,count(code) as count from tb_test group by code) as c
on c.code=ta.code and ta.count=c.count
)
--求连续
select d.code,min(st_date) as st_date,max(end_date) as end_date from tb
left join tb_test as d
on d.code=tb.code
group by d.code
--求其他
union all
select e.code,max(st_date),max(end_date) from tb_test e
where not exists(select code from tb f where f.code=e.code )
group by e.code
本人是新手,不管效率的话,这也算是整出来了!比上次多新增了两条数据,便于区分……