34,591
社区成员
发帖
与我相关
我的任务
分享
select b.[编号],
a.[计费日期],
b.[开始日期],
isnull(b.[结束日期],'')
from b b
left join a a
on a.[编号]=b.[编号]
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([编号] varchar(6),[计费日期] datetime)
insert [a]
select '001','2013-03-01' union all
select '001','2012-01-01'
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([编号] varchar(6),[开始日期] datetime,[结束日期] datetime)
insert [b]
select '001','2011-01-01','2013-03-01' union all
select '001','2013-03-01',null
--查询
select b.[编号],
a.[计费日期],
b.[开始日期],
b.[结束日期]
from b b
left join a a
on a.[编号]=b.[编号]
and a.[计费日期]>=b.[开始日期]
and a.[计费日期]<isnull(b.[结束日期],'9999-01-01')
编号 计费日期 开始日期 结束日期
------ ----------------------- ----------------------- -----------------------
001 2012-01-01 00:00:00.000 2011-01-01 00:00:00.000 2013-03-01 00:00:00.000
001 2013-03-01 00:00:00.000 2013-03-01 00:00:00.000 NULL
(2 行受影响)