drop table #a
drop table #b
create table #a( dt1 datetime,dt2 datetime)
insert into #a select '2013-01-01','2013-01-31'
insert into #a select '2013-02-10','2013-02-20'
insert into #a select '2013-03-05','2013-03-31'
insert into #a select '2013-04-01','2013-04-30'
create table #b( dt1 datetime,dt2 datetime)
insert into #b select '2013-01-10','2013-01-15'
insert into #b select '2013-02-05','2013-02-10'
insert into #b select '2013-03-06','2013-03-20'select * from #a
select * from #b#b 中的日期 若 不包含在#a 中则返回 1,反之返回0最后结果2013-01-10 2013-01-15 1
2013-02-05 2013-02-10 0
2013-03-06 2013-03-20 1
drop table #b
create table #a( dt1 datetime,dt2 datetime)
insert into #a select '2013-01-01','2013-01-31'
insert into #a select '2013-02-10','2013-02-20'
insert into #a select '2013-03-05','2013-03-31'
insert into #a select '2013-04-01','2013-04-30'
create table #b( dt1 datetime,dt2 datetime)
insert into #b select '2013-01-10','2013-01-15'
insert into #b select '2013-02-05','2013-02-10'
insert into #b select '2013-03-06','2013-03-20'select * from #a
select * from #b#b 中的日期 若 不包含在#a 中则返回 1,反之返回0最后结果2013-01-10 2013-01-15 1
2013-02-05 2013-02-10 0
2013-03-06 2013-03-20 1
gocreate table #a( dt1 datetime,dt2 datetime)
insert into #a select '2013-01-01','2013-01-31'
insert into #a select '2013-02-10','2013-02-20'
insert into #a select '2013-03-05','2013-03-31'
insert into #a select '2013-04-01','2013-04-30'
create table #b( dt1 datetime,dt2 datetime)
insert into #b select '2013-01-10','2013-01-15'
insert into #b select '2013-02-05','2013-02-10'
insert into #b select '2013-03-06','2013-03-20'goselect
b.*,Flag=case when a.dt1 is not null then 1 else 0 end
from #b as b
left join #a as a on a.dt1<=b.dt1 and a.dt2>=b.dt2/*
dt1 dt2 Flag
2013-01-10 00:00:00.000 2013-01-15 00:00:00.000 1
2013-02-05 00:00:00.000 2013-02-10 00:00:00.000 0
2013-03-06 00:00:00.000 2013-03-20 00:00:00.000 1
*/
drop table #b
create table #a(code char(10), dt1 datetime,dt2 datetime)
insert into #a select 'c001', '2013-01-01','2013-01-31'
insert into #a select 'c001', '2013-02-10','2013-02-20'
insert into #a select 'c001', '2013-03-05','2013-03-31'
insert into #a select 'c001', '2013-04-01','2013-04-30'insert into #a select 'c002', '2013-04-01','2013-04-30'
insert into #a select 'c002', '2013-05-01','2013-05-30'
create table #b(code char(10), dt1 datetime,dt2 datetime)
insert into #b select 'c001', '2013-01-10','2013-01-15'
insert into #b select 'c001', '2013-02-05','2013-02-10'
insert into #b select 'c001', '2013-03-06','2013-03-20'insert into #b select 'c002', '2013-03-10','2013-03-31'
insert into #b select 'c002', '2013-05-02','2013-05-20'
select * from #a
select * from #bc001 2013-01-10 00:00:00.000 2013-01-15 00:00:00.000 1
c001 2013-02-05 00:00:00.000 2013-02-10 00:00:00.000 0
c001 2013-03-06 00:00:00.000 2013-03-20 00:00:00.000 1
c002 2013-03-10 00:00:00.000 2013-03-31 00:00:00.000 0
c002 2013-05-02 00:00:00.000 2013-05-20 00:00:00.000 1
b.*,Flag=case when a.dt1 is not null then 1 else 0 end
from #b as b
left join #a as a on a.dt1<=b.dt1 and a.dt2>b.dt2a.dt2>b.dt2这里改为>就行了
b.*,Flag=case when a.dt1 is not null then 1 else 0 end
from #b as b
left join #a as a on a.dt1<=b.dt1 and a.dt2>=b.dt2 And a.code = b.code
--------------------------
c001 2013-01-10 00:00:00.000 2013-01-15 00:00:00.000 1
c001 2013-02-05 00:00:00.000 2013-02-10 00:00:00.000 0
c001 2013-03-06 00:00:00.000 2013-03-20 00:00:00.000 1
c002 2013-03-10 00:00:00.000 2013-03-31 00:00:00.000 0
c002 2013-05-02 00:00:00.000 2013-05-20 00:00:00.000 1
是要这样子吗?引用 roy_88