select * from t_czzl a inner join (select * from t_belongsto where '2005-02-06' between from and to) b on a.clbh=b.clbh
select a.* from (select clbh,sim,cph from t_czzl group by clbh,sim,cph having count(*)=1) a inner join T_belongsTo b on(a.clbh=b.clbh) where '2005-02-06'>from and '2005-02-06'<to
select distinct clbh,sim,cph from T_CZZL where clbh in (select clbh from T_belongsTo where getdate() between from and to)
create table ##T_CZZL ( [clbh] int,[sim] varchar(20),[cph] varchar(5) )insert into ##T_CZZL select '1234' , '13816075644' , 'A1234' union select '2345' , '13333333333' , 'B2345' union select '3456' , '13777777777' , 'C3456' create table ##T_belongsTo ( [clbh] int,[afrom] smalldatetime,[ato] smalldatetime )insert into ##T_belongsToselect '1234' , '2005-02-04' , '2005-02-21' union select '1234' , '2005-02-05' , '2005-02-27' union select '3456' , '2005-02-05' , '2005-02-27' select * from ##T_CZZL select * from ##T_belongsTodeclare @@Now smalldatetime set @@Now=getdate() --select convert(varchar(10),getdate(),120)--得到日期 print @@Now select * from ##T_CZZL a where exists (select * from ##T_belongsTo b where a.clbh=b.clbh and DATEDIFF(day,@@Now ,b.ato )>=0 and DATEDIFF(day,@@Now ,b.afrom )<=0 )drop table ##T_CZZL drop table ##T_belongsTo测试结果:clbh sim cph ----- ------------ ------------1234 13816075644 A1234 3456 13777777777 C3456
inner join (select * from t_belongsto where '2005-02-06' between from and to) b
on a.clbh=b.clbh
where '2005-02-06'>from and '2005-02-06'<to
where getdate() between from and to)
(
[clbh] int,[sim] varchar(20),[cph] varchar(5)
)insert into ##T_CZZL
select '1234' , '13816075644' , 'A1234' union
select '2345' , '13333333333' , 'B2345' union
select '3456' , '13777777777' , 'C3456' create table ##T_belongsTo
(
[clbh] int,[afrom] smalldatetime,[ato] smalldatetime
)insert into ##T_belongsToselect '1234' , '2005-02-04' , '2005-02-21' union
select '1234' , '2005-02-05' , '2005-02-27' union
select '3456' , '2005-02-05' , '2005-02-27' select * from ##T_CZZL
select * from ##T_belongsTodeclare @@Now smalldatetime
set @@Now=getdate() --select convert(varchar(10),getdate(),120)--得到日期
print @@Now select * from ##T_CZZL a where
exists (select * from ##T_belongsTo b
where a.clbh=b.clbh
and DATEDIFF(day,@@Now ,b.ato )>=0
and DATEDIFF(day,@@Now ,b.afrom )<=0
)drop table ##T_CZZL
drop table ##T_belongsTo测试结果:clbh sim cph
----- ------------ ------------1234 13816075644 A1234
3456 13777777777 C3456
on A.clbh=B.clbh
where from<='2005-02-06' and to>='2005-02-06'