Table1有两列:
id f
1 31
2 40
...
========================
Table2有四列:
id d1 d2 d
1 23 30 5
2 10 50 6
3 30 35 5
...编写一个sql语句,查到的数据满足下述条件:
f >= d1 and f <= d2 and d=5 table2中d=5的记录
有不确定的多个。即:(f >= 23 and f <= 30) or (f >= 30 and f <= 35) or ...
id f
1 31
2 40
...
========================
Table2有四列:
id d1 d2 d
1 23 30 5
2 10 50 6
3 30 35 5
...编写一个sql语句,查到的数据满足下述条件:
f >= d1 and f <= d2 and d=5 table2中d=5的记录
有不确定的多个。即:(f >= 23 and f <= 30) or (f >= 30 and f <= 35) or ...
select * from table1 a,table2 b where a.id=b.id and b.d=5 and a.f <=b.d2 and a.f >=b.d1
select t1.id, t1.f
from table1 t1 full join table2 t2 on t1.f>=t2.d1 and t1.f<=t2.d2
where t2.d=5 ) t;
--不是很明白需求,不知道是不是这样的?
--这也太简单了嘛
select * from tab1 a,tab2 b
where a.id=b.id and (f between d1 and d2) and d=5
create table table2(id number(18,0), d1 number(18,0), d2 number(18,0), d number(18,0));insert into table1(id,f) values(1,31);
insert into table1(id,f) values(2,40);insert into table2(id,d1,d2,d) values(1,23,30,5);
insert into table2(id,d1,d2,d) values(2,10,50,6);
insert into table2(id,d1,d2,d) values(3,30,35,5);commit;select distinct id, f from (
select t1.id, t1.f
from table1 t1 full join table2 t2 on t1.f>=t2.d1 and t1.f<=t2.d2
where t2.d=5 ) t;
from (
select t1.id,t1.f ,t2.id,t2.d1,t2.d2,t2.d
from Table1 t1 left join Table2 t2
)
where f>=d1 and f<=d2 and d=5