有关数据组合的问题

[干饭人之路]

有一个表T1，有6个字段，建表语句如下：
create table T1
( v1 varchar2(4),
v2 varchar2(4),
v3 varchar2(4),
v4 varchar2(4),
v5 varchar2(4),
v6 varchar2(4)
);
其记录如下：
v1 v2 v3 v4 v5 v6
01 09 10 11 18 40
；
现在要对T1中的这条记录取组合C6^5，
C6^5代表从6个号码中取5个，有多少种组合。C6^5也写做C(6,5)
用oracle sql该如何写能展示C(6,5)的所有组合？

[卖水果的net]

楼主想以什么样的形式展示出来呢？

[H_Gragon]

是不定这6列吗？

[H_Gragon]

固定这6列？

[干饭人之路]

select 或者 dbms_outpine都可以

[jdsnhan]

一句sql费劲，给你个参考的存储过程：

set serveroutput on 
SQL> select * from t1;
V1   V2   V3   V4   V5   V6
---- ---- ---- ---- ---- ----
01   09   10   11   18   40

SQL> 
SQL> create or replace procedure pt is
  2    res1 varchar2(4);
  3    res2 varchar2(4);
  4  begin
  5     declare
  6         cursor c_sql
  7         is
  8         select 'select '||substr(sys_connect_by_path(COLUMN_NAME,','),2)||' from t1' tsql from (select column_name from USER_tAB_COLS WHERE TABLE_NAME='T1')
  9         where level = 2
 10         connect BY prior column_name < column_name AND LEVEL <=2;
 11         c_row c_sql%rowtype;
 12  begin
 13         for c_row in c_sql loop
 14            execute immediate c_row.tsql into res1,res2;
 15           dbms_output.put_line(res1||','||res2) ;
 16         end loop;
 17  end;
 18  end pt;
 19  /
Procedure created

SQL> exec pt;
01,09
01,10
01,11
01,18
01,40
09,10
09,11
09,18
09,40
10,11
10,18
10,40
11,18
11,40
18,40
PL/SQL procedure successfully completed

[sxq129601]

WITH T AS (SELECT REGEXP_SUBSTR(v1 || ',' || v2 || ',' || v3 || ',' || v4 || ',' || v5 || ',' || v6, '[^,]+', 1, LEVEL) COL FROM T1 CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(v1 || ',' || v2 || ',' || v3 || ',' || v4 || ',' || v5 || ',' || v6, '[^,]+')) + 1 AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL) SELECT COL1, COL2 FROM (SELECT TT.COL COL1, TT2.COL COL2, ROW_NUMBER() OVER(PARTITION BY(TT.COL * TT2.COL) ORDER BY TT.COL) RN FROM T TT, T TT2 WHERE TT.COL <> TT2.COL) WHERE RN = 1