连TOM也答不上来的SQL问题，你来试试看！

〇〇

记录学习

kingtsi

赞

ahuhuge111

记录学习。可惜新项目的数据库是9的。不能用只能干着急

liuyh_137411

with f as (select 'a,b,c' col1 from dual)
, g as (select col  col1 ,substr(regexp_replace(','||f.col1,',['||col||']+'),2) col2 from
(
    select regexp_substr(col1,'[^,]+',1,rownum) col from f
   connect by rownum<=length(regexp_replace(col1,'[^,]+'))
      ) w,f)
select col1,col2 from g
union
  select col2,col1 from g

liuyh_137411

COL1       COL2
---------- ---------
a          b,c
a,b        c
a,c        b
b          a,c
b,c        a
c          a,b

Hopewell_Go

呵呵。。考验 算法啊。。

fyfeng

9i的，貌似用不了正则

newkid

原帖由 liuyh_137411 于 2010-4-22 12:27 发表
with f as (select 'a,b,c' col1 from dual)
, g as (select col  col1 ,substr(regexp_replace(','||f.col1,',['||col||']+'),2) col2 from
(
    select regexp_substr(col1,'[^,]+',1,rownum) col from f
   connect by rownum

这个写法如果多于三个元素就不行了。

lastwinner

貌似我的和dingjun的有些相似，解析逗号是在之前的老帖子里翻开出来的(http://www.itpub.net/viewthread.php?tid=355415 ， 五年前的帖啊，感慨颇多)，懒得改什么了，所以赋值方法也和〇〇的一样
下面开始献丑：
var tt varchar2(30);
exec :tt := 'a,b,c,d,e';
column th format a5
column xmlpath format a15
column xmlpath2 format a15
with x as (select th, row_number()over(order by th) rn from (select substr(:tt,st+1,en-st-1) th from (select decode(rn,1,0,instr(:tt,',',1,rn-1)) st ,decode(instr(:tt,',',1,rn),0,length(:tt)+1, instr(:tt,',',1,rn)) en from (select rownum rn from dual connect by rownum<=length(:tt)-length(replace(:tt,',',''))+1))))
select replace(xmlpath,',','') xmlpath, replace(translate(:tt, xmlpath, ' '),' ','') xmlpath2 from (select th, sys_connect_by_path(th,',') xmlpath from x connect by rn<prior rn);

具体的运行效果可见下。10g R2上运行通过，9i R2以上也应该能通过
[php]
TJ@lw.lw> column th format a5
TJ@lw.lw> column xmlpath format a15
TJ@lw.lw> column xmlpath2 format a15
TJ@lw.lw> with x as (select th, row_number()over(order by th) rn from (select su
bstr(:tt,st+1,en-st-1) th from (select decode(rn,1,0,instr(:tt,',',1,rn-1)) st ,
decode(instr(:tt,',',1,rn),0,length(:tt)+1, instr(:tt,',',1,rn)) en from (select
rownum rn from dual connect by rownum<=length(:tt)-length(replace(:tt,',',''))+
1))))
  2  select replace(xmlpath,',','') xmlpath, replace(translate(:tt, xmlpath, ' '
),' ','') xmlpath2 from (select th, sys_connect_by_path(th,',') xmlpath from x c
onnect by rn<prior rn);

XMLPATH         XMLPATH2
--------------- ---------------
a               bcde
b               acde
ba              cde
c               abde
ca              bde
cb              ade
cba             de
d               abce
da              bce
db              ace
dba             ce

XMLPATH         XMLPATH2
--------------- ---------------
dc              abe
dca             be
dcb             ae
dcba            e
e               abcd
ea              bcd
eb              acd
eba             cd
ec              abd
eca             bd
ecb             ad

XMLPATH         XMLPATH2
--------------- ---------------
ecba            d
ed              abc
eda             bc
edb             ac
edba            c
edc             ab
edca            b
edcb            a
edcba

已选择31行。

已用时间:  00: 00: 00.04
TJ@lw.lw> exec :tt := 'a,b,c,d,e,f,g,h,i,j';

PL/SQL 过程已成功完成。

已用时间:  00: 00: 00.00
TJ@lw.lw> /

XMLPATH         XMLPATH2
--------------- ---------------
a               bcdefghij
b               acdefghij
ba              cdefghij
c               abdefghij
ca              bdefghij
.......................................
jihgfd          abce
jihgfda         bce

XMLPATH         XMLPATH2
--------------- ---------------
jihgfdb         ace
jihgfdba        ce
jihgfdc         abe
jihgfdca        be
jihgfdcb        ae
jihgfdcba       e
jihgfe          abcd
jihgfea         bcd
jihgfeb         acd
jihgfeba        cd
jihgfec         abd

XMLPATH         XMLPATH2
--------------- ---------------
jihgfeca        bd
jihgfecb        ad
jihgfecba       d
jihgfed         abc
jihgfeda        bc
jihgfedb        ac
jihgfedba       c
jihgfedc        ab
jihgfedca       b
jihgfedcb       a
jihgfedcba

已选择1023行。

已用时间:  00: 00: 01.45
....................................................
.............
.
.
.

[/php]



想保留逗号，对xmlpath就用trim(',' from filed1)方法，对xmlpath2就用regexp_replace（这个当然是10g以上才能支持啦）

[ 本帖最后由 lastwinner 于 2010-4-23 01:46 编辑 ]

newkid

嗯，还是用CONNECT BY求全排列，用TRANSLATE求补集（只适用于单字符）。
解析方法用了INSTR, SUBSTR适用于9I.