請教各位高手,如果用P/L SQL得到一組數據的所有組合情況並存到一個table中?
例如:
初始數據: 1,2,3,4 (這個初始數據可能是n個)
想要的結果
batch_id no
1 1
2 2
3 3
4 4
5 1
5 2
6 1
6 3
... ...
以此類推請大家幫幫忙 我想了好幾天也沒想出來
例如:
初始數據: 1,2,3,4 (這個初始數據可能是n個)
想要的結果
batch_id no
1 1
2 2
3 3
4 4
5 1
5 2
6 1
6 3
... ...
以此類推請大家幫幫忙 我想了好幾天也沒想出來
create table test1(Number1 number)
create table test2(Number2 number)test1,test2中分别有数据[1,2,3,4,5,6]select t1.Number1,t2.Number2 from test1 t1,test2 t2;
(1),(2),(3),(4),(1,2),(1,3),(1,4),(1,2,3),(1,2,4)...
batch_id只是一個組合的編號,以便之後可以取出某種滿足條件的組合內容。
SQL> select * from t1; N
----------
1
2
3
4SQL> select substr(SYS_CONNECT_BY_PATH(n,','),2) from t1 connect by prior n<n;SUBSTR(SYS_CONNECT_BY_PATH(N,','),2)
--------------------------------------------------------------------------------
1
1,2
1,2,3
1,2,3,4
1,2,4
1,3
1,3,4
1,4
2
2,3
2,3,4
2,4
3
3,4
415 rows selected.
CC:swallow_x ()
(1,2,1)应该也是一个组合吧?
太強了 太感謝了 可是我不太理解 能否請您再解釋一下呢?
有錯誤:identifier 'SYS_CONNECT_BY_PATH' must be declared
要怎麼解決呢?
(select 1 a from dual union select null from dual) a,
(select 2 b from dual union select null from dual) b,
(select 3 c from dual union select null from dual) c,
(select 4 d from dual union select null from dual) d
(
select a.a||b.b||c.c||d.d xx from
(select 1 a from dual union select null from dual) a,
(select 2 b from dual union select null from dual) b,
(select 3 c from dual union select null from dual) c,
(select 4 d from dual union select null from dual) d
where a.a||b.b||c.c||d.d is not null
)
你数据库的版本
單獨執行就可以的 是因為版本太低了嗎?
create table t1 as select rownum r from dual connect by rownum<=5;
create procedure p1(maxlevel number:=0)
as
v_maxlevel number;
begin
if maxlevel<=0 then
select count(*) into v_maxlevel from t1;
else
v_maxlevel:=maxlevel;
end if;
for t in (select substr(SYS_CONNECT_BY_PATH(r,','),2) s from t1 where level<=v_maxlevel connect by prior r<r) loop
dbms_output.put_line(t.s);
end loop;
end;
/
set serveroutput on
exec p1
我現在用另一個方法實現了
謝謝你