select t1.类别+t2.类别 from t t1,t t2 where t1.类别< t2.类别
我要得到的是 a a+b a+b+c a+b+c+d b b+c b+c+d a+c a+d 等等,这样的组合!!!1
使用function来解决吧。----- 这里是运算排列组合的函数,使用了递归,table和循环的知识点 create or replace function sample_table(vt varchar_table) return varchar_table is v varchar_table; v1 varchar_table; v2 varchar_table; len number; begin if(vt is null or vt.count = 0) then dbms_output.put_line('No'); return vt; else if(vt.count = 1) then return vt; else v1 :=new varchar_table(); v1.extend(vt.count-1); for i in 1 .. vt.count-1 loop v1(i) := vt(i); end loop; end if; end if;
v2 := sample_table(v1); v := new varchar_table();
len := v2.count; v.extend(v2.count*2+1);
v(len + 1) := vt(vt.count); for i in 1 .. len loop v(i) := v2(i); v(len + 1 + i) := v2(i)||'+'||vt(vt.count); end loop;
return(v); end sample_table;
接着上面的create or replace function rowset2array return varchar_table is Result varchar_table := new varchar_table(); cursor c is select name from t2; v varchar(4000); begin open c;
loop fetch c into v; exit when c%NOTFOUND; result.extend(); result(result.count) := v; end loop;
return(Result); end rowset2array; /SQL> create table t2 (name varchar2(10));SQL> insert into t2 values('a'); SQL> insert into t2 values('b'); SQL> insert into t2 values('c'); SQL> commit;SQL> select name from t2;NAME ---------- a b cSQL> select * from table(sample_table(rowset2array)); COLUMN_VALUE ------------- a b a+b c a+c b+c a+b+c
SQL> insert into t2 values('d'); SQL> select * from t2; NAME ---------- a b c d SQL> select * from table(sample_table(rowset2array));COLUMN_VALUE ---------------------------------------------------------a b a+b c a+c b+c a+b+c d a+d b+d a+b+dCOLUMN_VALUE ---------------------------------------------------------c+d a+c+d b+c+d a+b+c+d已选择15行。
a
a+b
a+b+c
a+b+c+d
b
b+c
b+c+d
a+c
a+d
等等,这样的组合!!!1
create or replace function sample_table(vt varchar_table) return varchar_table is
v varchar_table;
v1 varchar_table;
v2 varchar_table;
len number;
begin
if(vt is null or vt.count = 0) then
dbms_output.put_line('No');
return vt;
else
if(vt.count = 1) then
return vt;
else
v1 :=new varchar_table();
v1.extend(vt.count-1);
for i in 1 .. vt.count-1 loop
v1(i) := vt(i);
end loop;
end if;
end if;
v2 := sample_table(v1);
v := new varchar_table();
len := v2.count;
v.extend(v2.count*2+1);
v(len + 1) := vt(vt.count);
for i in 1 .. len loop
v(i) := v2(i);
v(len + 1 + i) := v2(i)||'+'||vt(vt.count);
end loop;
return(v);
end sample_table;
Result varchar_table := new varchar_table();
cursor c is select name from t2;
v varchar(4000);
begin
open c;
loop
fetch c into v;
exit when c%NOTFOUND;
result.extend();
result(result.count) := v;
end loop;
return(Result);
end rowset2array;
/SQL> create table t2 (name varchar2(10));SQL> insert into t2 values('a');
SQL> insert into t2 values('b');
SQL> insert into t2 values('c');
SQL> commit;SQL> select name from t2;NAME
----------
a
b
cSQL> select * from table(sample_table(rowset2array));
COLUMN_VALUE
-------------
a
b
a+b
c
a+c
b+c
a+b+c
SQL> select * from t2;
NAME
----------
a
b
c
d
SQL> select * from table(sample_table(rowset2array));COLUMN_VALUE
---------------------------------------------------------a
b
a+b
c
a+c
b+c
a+b+c
d
a+d
b+d
a+b+dCOLUMN_VALUE
---------------------------------------------------------c+d
a+c+d
b+c+d
a+b+c+d已选择15行。