先写了一个只考虑value1的，参考 v_value1为table1中value1的值 SELECT RN,T2.* FROM( select PATH,ROW_NUMBER()OVER(ORDER BY PATH) AS RN from( select t2.*,sys_connect_by_path(id,',') path from t2 start with startvalue1=13 connect by prior endvalue1>v_value1 and prior endvalue1>endvalue1 and prior endvalue1<=startvalue1+1 and prior startvalue1-1>=startvalue1) where v_value1 between endvalue1 and startvalue1) A,T2 WHERE INSTR(A.PATH||',',','||T2.ID||',')>0;
假如我取table1的id 1，怎样得到结果，结果是啥
结果插入table3中
id questionid answerid sequence
1 1 1 1
2 1 4 2如果取table1的id为2的话，结果为：
id questionid answerid sequence // sequence表示第几套方案。
3 2 1 1
4 2 2 1
5 2 3 1
6 2 4 2
7 2 5 2
8 2 6 2
9 2 1 3
10 2 2 3
11 2 6 3
12 2 1 4
13 2 2 4
14 2 7 4
14 2 8 4
12 2 4 5
13 2 5 5
14 2 7 5
14 2 8 5
v_value1为table1中value1的值
SELECT RN,T2.* FROM(
select PATH,ROW_NUMBER()OVER(ORDER BY PATH) AS RN from(
select t2.*,sys_connect_by_path(id,',') path from t2
start with startvalue1=13
connect by prior endvalue1>v_value1
and prior endvalue1>endvalue1
and prior endvalue1<=startvalue1+1
and prior startvalue1-1>=startvalue1)
where v_value1 between endvalue1 and startvalue1) A,T2
WHERE INSTR(A.PATH||',',','||T2.ID||',')>0;
WITH T AS (
SELECT A.PATH APATH,B.PATH BPATH,ROW_NUMBER()OVER(ORDER BY A.RN,B.RN) AS RN FROM
(select PATH,ROW_NUMBER()OVER(ORDER BY PATH) AS RN,TR from(
select t2.*,sys_connect_by_path(id,',') path,connect_by_root(id) tr
from t2
start with startvalue1=13
connect by prior endvalue1>10
and prior endvalue1>endvalue1
and prior endvalue1<=startvalue1+1
and prior startvalue1-1>=startvalue1)
where 10 between endvalue1 and startvalue1) A,
(select PATH,ROW_NUMBER()OVER(ORDER BY PATH) AS RN,TR from(
select t2.*,sys_connect_by_path(id,',') path,connect_by_root(id) tr
from t2
start with startvalue2=13
connect by prior endvalue2>7
and prior endvalue2>endvalue2
and prior endvalue2<=startvalue2+1
and prior startvalue2-1>=startvalue2)
where 7 between endvalue2 and startvalue2) B
where a.tr=b.tr
)
SELECT T.RN,T2.* FROM T,T2
WHERE INSTR(T.APATH||',',','||T2.ID||',')>0 OR INSTR(T.BPATH||',',','||T2.ID||',')>0
ORDER BY T.RN,T2.ID;
测试结果
RN ID STARTVALUE1 ENDVALUE1 STARTVALUE2 ENDVALUE2
---------- ---------- ----------- ---------- ----------- ----------
1 1 13 10 13 12
1 2 9 8 11 10
1 3 7 5 9 6
2 1 13 10 13 12
2 2 9 8 11 10
2 6 9 8 9 7
3 1 13 10 13 12
3 2 9 8 11 10
3 7 9 7 9 8
3 8 6 5 7 5
4 1 13 10 13 12 RN ID STARTVALUE1 ENDVALUE1 STARTVALUE2 ENDVALUE2
---------- ---------- ----------- ---------- ----------- ----------
4 3 7 5 9 6
4 5 11 10 12 10
5 1 13 10 13 12
5 5 11 10 12 10
5 6 9 8 9 7
6 1 13 10 13 12
6 5 11 10 12 10
6 7 9 7 9 8
6 8 6 5 7 5
7 2 9 8 11 10
7 3 7 5 9 6 RN ID STARTVALUE1 ENDVALUE1 STARTVALUE2 ENDVALUE2
---------- ---------- ----------- ---------- ----------- ----------
7 4 13 12 13 12
7 5 11 10 12 10
8 2 9 8 11 10
8 4 13 12 13 12
8 5 11 10 12 10
8 6 9 8 9 7
9 2 9 8 11 10
9 4 13 12 13 12
9 5 11 10 12 10
9 7 9 7 9 8
9 8 6 5 7 5 RN ID STARTVALUE1 ENDVALUE1 STARTVALUE2 ENDVALUE2
---------- ---------- ----------- ---------- ----------- ----------
10 3 7 5 9 6
10 4 13 12 13 12
10 5 11 10 12 10
11 4 13 12 13 12
11 5 11 10 12 10
11 6 9 8 9 7
12 4 13 12 13 12
12 5 11 10 12 10
12 7 9 7 9 8
12 8 6 5 7 5已选择43行。RN表示第几套方案