表TB1
start end
-------------------
1 3
11 13
21 23
......TB2
id
-----
1
2
3
......
n求SQL语句查询TB2的结果是在TB1的范围中(结果应该是1,2,3,11,12,13,21,22,23......)
start end
-------------------
1 3
11 13
21 23
......TB2
id
-----
1
2
3
......
n求SQL语句查询TB2的结果是在TB1的范围中(结果应该是1,2,3,11,12,13,21,22,23......)
WHERE EXISTS
(SELECT 1 FROM TB1 T1
WHERE T2.ID >=T1.START AND T2.ID <=T1.END)
create table t1 (s number(5), e number(5));
insert into t1 values (1,3);
insert into t1 values (5,6);
insert into t1 values (8,11);
select a.l
from t1,(select level l from dual connect by level < 50) a
where a.l between s and e L
----------------
1 1
2 2
3 3
4 5
5 6
6 8
7 9
8 10
9 11
CREATE TABLE T2(tid NUMBER);
插入数据(10000行):T1:
BEGIN
FOR i IN 1..10000 LOOP
insert into t2 values(i);
END LOOP;
END;
T2:
DECLARE
i INT:=1;
j INT:=3;
BEGIN
FOR k IN 1..10000 LOOP
insert into t1 values(i,j);
i:=i+10;
j:=j+10;
END LOOP;
END;
我在SQL DEVELOPER里面没用多长时间,不知道是否是我理解错你的需求了,但是结果应该跟你想要的一样。select tid from t2,t1
where t2.tid>=t1.s and t2.tid<=t1.e错了勿喷啊~