if object_id('[firstbegin]') is not null drop table [firstbegin]
go
create table [firstbegin]([id] int,[firststarttime] datetime)
insert [firstbegin]
select 1,'2011-11-22 10:45:01.000' union all
select 2,'2011-12-01 10:45:01.000' union all
select 3,'2011-12-01 10:45:01.000'

if object_id('[firstend]') is not null drop table [firstend]
go
create table [firstend]([id] int,[firstendtime] datetime)
insert [firstend]
select 1,'2011-11-30 10:45:01.000' union all
select 2,'2011-12-08 10:45:01.000' union all
select 3,'2011-12-09 10:45:01.000'

if object_id('[secondbegin]') is not null drop table [secondbegin]
go
create table [secondbegin]([id] int,[secondstarttime] datetime)
insert [secondbegin]
select 1,'2011-11-19 11:45:01.000' union all
select 2,'2011-12-09 11:45:01.000' union all
select 3,null

if object_id('[secondend]') is not null drop table [secondend]
go
create table [secondend]([id] int,[secondendtime] datetime)
insert [secondend]
select 1,'2011-11-30 11:45:01.000' union all
select 2,null union all
select 3,null

select a.id,a.firststarttime,b.firstendtime,c.secondstarttime,d.secondendtime
from firstbegin a
left join firstend b on a.id=b.id
left join secondbegin c on a.id=c.id
left join secondend d on a.id=d.id
where d.secondendtime is null

/**
id          firststarttime          firstendtime            secondstarttime         secondendtime
----------- ----------------------- ----------------------- ----------------------- -----------------------
2           2011-12-01 10:45:01.000 2011-12-08 10:45:01.000 2011-12-09 11:45:01.000 NULL
3           2011-12-01 10:45:01.000 2011-12-09 10:45:01.000 NULL                    NULL

(2 行受影响)
**/

------解决方案--------------------

SQL code

--> 测试数据: @firstbegin
declare @firstbegin table (id int,firststarttime datetime)
insert into @firstbegin
select 1,'2011-11-22 10:45:01.000' union all
select 2,'2011-12-01 10:45:01.000' union all
select 3,'2011-12-01 10:45:01.000'

--> 测试数据: @firstend
declare @firstend table (id int,firstendtime datetime)
insert into @firstend
select 1,'2011-11-30 10:45:01.000' union all
select 2,'2011-12-08 10:45:01.000' union all
select 3,'2011-12-09 10:45:01.000'

--> 测试数据: @secondbegin
declare @secondbegin table (id int,secondstarttime datetime)
insert into @secondbegin
select 1,'2011-11-19 11:45:01.000' union all
select 2,'2011-12-09 11:45:01.000' union all
select 3,null

--> 测试数据: @secondend
declare @secondend table (id int,secondendtime datetime)
insert into @secondend
select 1,'2011-11-30 11:45:01.000' union all
select 2,null union all
select 3,null

select a.*,b.firstendtime,c.secondstarttime,d.secondendtime from @firstbegin a 
left join @firstend b on a.id=b.id
left join @secondbegin c on a.id=c.id
left join @secondend d on a.id=d.id
where a.firststarttime between '2011-12-01' and '2011-12-10'

/*
id          firststarttime          firstendtime            secondstarttime         secondendtime
----------- ----------------------- ----------------------- ----------------------- -----------------------
2           2011-12-01 10:45:01.000 2011-12-08 10:45:01.000 2011-12-09 11:45:01.000 NULL
3           2011-12-01 10:45:01.000 2011-12-09 10:45:01.000 NULL                    NULL
*/

------解决方案--------------------

探讨

引用:

SQL code

--> 测试数据: @firstbegin
declare @firstbegin table (id int,firststarttime datetime)
insert into @firstbegin
select 1,'2011-11-22 10:45:01.000' union all
select 2,……