Using SYS_CONNECT_BY_PATH to construct the combinations

在ITpub上看到这么一个问题....

写一个求和的程序，谢谢

we have a list of integer {1, 2, 3, 4, 5, 6, 7, 8, 9 }. Set each item as R, where i = 1..9. 

write code to return each possible combination whose sum is less or equal to 25.

For example,

1;
1,2;
1, 2, 3;
....

想了一下，大概的思路是构造这9个数字所有的可能组合情况，然后再进行过滤(sum<=25)， 关于创建组合情况，可以考虑用sys_connect_by_path (和row_number函数， 有的时候需要用这个函数来构造层次关系）来办到....

with array9 as
(selectlevel rn
 from dual
 connect bylevel<=9)

select
  level_path
from 
( 
select 
 ltrim(sys_connect_by_path(rn, ','), ',') level_path
from array9
connect by prior rn < rn
)

但是怎么去过滤这个结果呢，很容易想到可以通过创建一个函数来解决 （想了半天也没想到怎么写SQL来解决，杯具...)

createorreplacefunction test_sum(p_str invarchar2)  return pls_integer 
deterministic
as
    l_index pls_integer;
    l_str varchar2(32767);
    l_sum pls_integer :=0;
    l_str_token varchar2(100);
begin
    l_str := p_str;
    l_index := instr(l_str, ',');
    while l_index >0
    loop
        l_str_token := substr(l_str, 1, l_index-1);
        l_sum := l_sum + to_number(l_str_token);
        l_str := substr(l_str, l_index+1);
        l_index := instr(l_str, ',');
    end loop;
    
    return l_sum;
end;
/

有了这个函数帮忙，上面的SQL就很容易写了....

with array9 as
(selectlevel rn
 from dual
 connect bylevel<=9)

select
  level_path
from 
( 
select 
 ltrim(sys_connect_by_path(rn, ','), ',') level_path
from array9
connect by prior rn < rn
)
where  test_sum(level_path ||',') <=25;

不过后来看到dingjun123 给出了一个解决方法，真是不错，借用了笛卡尔乘积, 然后就可以很容易通过SUM函数来进行过滤了....

SQL>WITH t AS
  2  (SELECTLEVEL val FROM dual
  3  CONNECT BYLEVEL<10)
  4SELECT   trim(both ','FROM a.val_list) val_list
  5FROM
  6   (SELECT SYS_CONNECT_BY_PATH (val, ',') ||',' val_list
  7FROM   t
  8               CONNECT BY  val > PRIOR val) a, t b
  9WHERE    INSTR (a.val_list, ','|| b.val ||',') >0
10GROUPBY a.val_list
11HAVINGSUM (b.val) <=25
12ORDERBY1;