日期:2014-05-17 浏览次数:20473 次
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#tb01') is null
drop table #tb01
Go
Create table #tb01([ddate] Datetime,[instartno] int,[inendno] int)
Insert #tb01
select '2012-12-6',5000,8000 union all
select '2012-12-6',10000,20000 union all
select '2012-12-8',60000,90000
Go
if not object_id(N'Tempdb..#tb02') is null
drop table #tb02
Go
Create table #tb02([ddate] Datetime,[outstartno] int,[outendno] int)
Insert #tb02
select '2012-12-7',10050,16000 union all --改改测试数据
select '2012-12-12',60000,70000 union all
select '2012-12-25',80000,85000
Go
;with a
as
(
select
case when b.[outendno] is not null then b.outendno+1 else a.instartno end as StartNr,
case when b.[outendno] is not null then a.inendno else a.inendno end as EndNr
from #tb01 as a
left join #tb02 as b on b.[outendno]>a.[instartno] and a.[inendno]>b.[outstartno] and b.[outendno]<a.[inendno] and b.ddate<='2012-12-31'
where a.ddate<='2012-12-31'
),b as
(
select
a.[instartno] as StartNr,
b.[outstartno]-1 as EndNr
from #tb01 as a
inner join #tb02 as b on b.[outendno]>a.[instartno] and a.[inendno]>b.[outstartno] and b.[outstartno]>a.[instartno]
where a.ddate<='2012-12-31' and b.ddate<='2012-12-31'
)
select
a.StartNr,
case when b.EndNr is not null then b.EndNr else a.EndNr end as EndNr
from a
left join b on a.EndNr>b.StartNr and b.EndNr>a.StartNr
union
select
StartNr,EndNr
from b
where not exists(select 1 from a where a.EndNr>b.StartNr and b.EndNr>a.StartNr)
/*
StartNr EndNr
5000 8000
10000 10049
16001 20000
70001 79999
85001 90000
*/