大家好。最近遇到难题。表数据如下图。
no leaveDate leaveType
1 2010-01-01 出差
2 2010-01-02 出差
4 2010-01-04 出差
5 2010-01-05 其他
6 2010-01-06 出差 我要得到的数据
no beginDate endDate leaveType
1 2010-01-01 2010-01-02 出差
2 2010-01-04 2010-01-04 出差
3 2010-01-05 2010-01-05 其他
4 2010-01-06 2010-01-06 出差
no leaveDate leaveType
1 2010-01-01 出差
2 2010-01-02 出差
4 2010-01-04 出差
5 2010-01-05 其他
6 2010-01-06 出差 我要得到的数据
no beginDate endDate leaveType
1 2010-01-01 2010-01-02 出差
2 2010-01-04 2010-01-04 出差
3 2010-01-05 2010-01-05 其他
4 2010-01-06 2010-01-06 出差
if object_id('tempdb.dbo.#ta') is not null drop table #ta
go
create table #ta (no int,leaveDate datetime,leaveType varchar(4))
insert into #ta
select 1,'2010-01-01','出差' union all
select 2,'2010-01-02','出差' union all
select 4,'2010-01-04','出差' union all
select 5,'2010-01-05','其他' union all
select 6,'2010-01-06','出差'select begindate=a.leaveDate,
endDate=min(b.leaveDate),
a.leaveType
from
(
select * from #ta t
where not exists(select * from #ta where leaveType=t.leaveType and datediff(day,leaveDate,t.leaveDate)=1)
)a,
(
select * from #ta t
where not exists(select * from #ta where leaveType=t.leaveType and datediff(day,leaveDate,t.leaveDate)=-1)
)b
where a.leaveType=b.leaveType and a.leaveDate<=b.leaveDate
group by a.leaveDate,a.leaveType
begindate endDate leaveType
----------------------- ----------------------- ---------
2010-01-01 00:00:00.000 2010-01-02 00:00:00.000 出差
2010-01-04 00:00:00.000 2010-01-04 00:00:00.000 出差
2010-01-06 00:00:00.000 2010-01-06 00:00:00.000 出差
2010-01-05 00:00:00.000 2010-01-05 00:00:00.000 其他(4 行受影响)
1 2010-01-01 出差
2 2010-01-02 出差
3 2010-01-03 出差
4 2010-01-04 出差
5 2010-01-05 其他
6 2010-01-06 出差
7 2010-01-07 出差
8 2010-02-08 出差
9 2010-02-09 出差
10 2010-02-10 出差
11 2010-02-11 出差
12 2010-02-12 出差
13 2010-01-04 出差
1。查询条件 2010-01-02 2010-02-09显示结果如下:
no beginDate endDate leaveType
1 2010-01-02 2010-01-04 出差
2 2010-01-05 2010-01-05 其他
3 2010-01-06 2010-01-07 出差
4 2010-02-08 2010-02-09 出差
if object_id('tempdb.dbo.#ta') is not null drop table #ta
go
create table #ta (no int,leaveDate datetime,leaveType varchar(4))
insert into #ta
select 1,'2010-01-01','出差' union all
select 2,'2010-01-02','出差' union all
select 3,'2010-01-03','出差' union all
select 4,'2010-01-04','出差' union all
select 5,'2010-01-05','其他' union all
select 6,'2010-01-06','出差' union all
select 7,'2010-01-07','出差' union all
select 8,'2010-02-08','出差' union all
select 9,'2010-02-09','出差' union all
select 10,'2010-02-10','出差' union all
select 11,'2010-02-11','出差' union all
select 12,'2010-02-12','出差'select begindate=a.leaveDate,
endDate=min(b.leaveDate),
a.leaveType
from
(
select * from #ta t
where leaveDate between '2010-01-02' and '2010-02-09'
and not exists(select * from #ta where leaveType=t.leaveType and datediff(day,leaveDate,t.leaveDate)=1 and leaveDate between '2010-01-02' and '2010-02-09')
)a,
(
select * from #ta t
where leaveDate between '2010-01-02' and '2010-02-09'
and not exists(select * from #ta where leaveType=t.leaveType and datediff(day,leaveDate,t.leaveDate)=-1 and leaveDate between '2010-01-02' and '2010-02-09')
)b
where a.leaveType=b.leaveType and a.leaveDate<=b.leaveDate
group by a.leaveDate,a.leaveType
order by begindatebegindate endDate leaveType
----------------------- ----------------------- ---------
2010-01-02 00:00:00.000 2010-01-04 00:00:00.000 出差
2010-01-05 00:00:00.000 2010-01-05 00:00:00.000 其他
2010-01-06 00:00:00.000 2010-01-07 00:00:00.000 出差
2010-02-08 00:00:00.000 2010-02-09 00:00:00.000 出差(4 行受影响)