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

〇〇

有的最后多1个,

COL1       COL2
---------- ---------
a          b,c,d,e
a,b        c,d,e
a,b,c      d,e
a,b,c,d    e
a,b,c,e    d,
a,b,d      c,e
a,b,d,e    c,
a,b,e      c,d,
a,c        b,d,e
a,c,d      b,e
a,c,d,e    b,

再套一个RTRIM

with tmp as
    (select  TRIM(REGEXP_SUBSTR(:x, '[^,]+', 1, level)) val from dual
       connect by level <= length(regexp_replace(:x,'[^,]*'))+1)
select LTRIM(col1,',') col1,
       RTRIM(LTRIM(REGEXP_REPLACE(translate(:x,col1,','),'(,){2,}', ','),','),',') col2
  from (select sys_connect_by_path (val, ',') col1
          from tmp
        connect by val > prior val )
where LTRIM(translate(:x,col1,','),',') is not null
order by col1;

[ 本帖最后由 〇〇 于 2010-4-9 14:24 编辑 ]

jimn1982

tom看了都会傻了
各位很牛

ocpdba591

scott@ORCL>select * from v$version where rownum<=2;

BANNER
----------------------------------------------------------------
Personal Oracle Database 10g Release 10.2.0.1.0 - Production
PL/SQL Release 10.2.0.1.0 - Production



create table t(x varchar2(10));

create or replace procedure test(x in varchar2)
     as
    type test_table is table of varchar2(4000) index by binary_integer;
    l_test_table test_table;
    l_test_table1 test_table;
    cursor c_test is select * from t;
  begin
    for i in 1..10000 loop
      select  substr(x,round(dbms_random.value(1,length(x))),round(dbms_random.value(1,length(x)))) into l_test_table(i) from dual;
      insert into  t values(l_test_table(i));
    end loop;
delete from t where rowid not in (select min(rowid) from t group by x);
  open c_test;
  fetch c_test bulk collect into l_test_table1;
    for i in 1..l_test_table1.count loop
     dbms_output.put_line(l_test_table1(i)||'     '||replace(translate(x,l_test_table1(i),'##########'),'#',''));
   end loop;
delete from t;
  close c_test;
  end;

scott@ORCL>exec test('abcde');
d     abce
bcde     a
e     abcd
cde     ab
de     abc
cd     abe
bcd     ae
b     acde
c     abde
abc     de
bc     ade
a     bcde
abcd     e
abcde
ab     cde

PL/SQL 过程已成功完成。

呵呵，见笑了。

[ 本帖最后由 ocpdba591 于 2010-4-9 15:39 编辑 ]

jetgm

oh

durexlollipop

amazing

〇〇

bd ace没有算出来

ocpdba591

呵呵，是的；除非打很多枪，否则打不中的

[ 本帖最后由 ocpdba591 于 2010-4-9 17:49 编辑 ]

nyfor

原帖由 ocpdba591 于 2010-4-9 17:48 发表
呵呵，是的；除非打很多枪，否则打不中的

newkid

哇！真是八仙过海各显神通，我隆重宣布开发版进入鼎盛时期！

以下是我的几种方法，等会再和各位的对照一下：

VAR s VARCHAR2(200);
EXEC :s := 'A,B,C,D,E,F';

用BITAND的思路：
WITH t AS (
SELECT REGEXP_SUBSTR(:s,'[^,]+',1,ROWNUM) s,COUNT(*) OVER() cnt, ROWNUM rn
  FROM DUAL
CONNECT BY ROWNUM<=LENGTH(:s)-LENGTH(REPLACE(:s,','))+1
)
, t2 AS (
SELECT ROWNUM id
  FROM DUAL
CONNECT BY ROWNUM < POWER(2, (SELECT count(*) FROM t)) - 1
)
, bits AS (
SELECT id, BITAND(id, POWER(2, rn)) bit,s
FROM t, t2
)
SELECT id,WMSYS.WM_CONCAT(CASE WHEN bit=0 THEN s END),WMSYS.WM_CONCAT(CASE WHEN bit<>0 THEN s END)
  FROM bits
GROUP BY id;

-----------------------------------------

假设全部元素都为单字符，用TRANSLATE求补集：
WITH t AS (
SELECT REGEXP_SUBSTR(:s,'[^,]+',1,ROWNUM) s,COUNT(*) OVER() cnt
  FROM DUAL
CONNECT BY ROWNUM<=LENGTH(:s)-LENGTH(REPLACE(:s,','))+1
)
,t2 AS (
SELECT SYS_CONNECT_BY_PATH(s,',') s2
  FROM t
CONNECT BY s>PRIOR s AND LEVEL<cnt
)
SELECT LTRIM(s2,',') AS COL1
      ,RTRIM(LTRIM(REGEXP_REPLACE(TRANSLATE(:s,s2,LPAD(',',LENGTH(s2),',')),'(,){2,}','\1'),','),',') AS COL2
  FROM t2;

-----------------------
列转行，用分区外连接求补集，再用WM_CONCAT拼接：
WITH t AS (
SELECT REGEXP_SUBSTR(:s,'[^,]+',1,ROWNUM) s,COUNT(*) OVER() cnt
  FROM DUAL
CONNECT BY ROWNUM<=LENGTH(:s)-LENGTH(REPLACE(:s,','))+1
)
,t2 AS (
SELECT ROWNUM AS id, SYS_CONNECT_BY_PATH(s,',') AS s2, LEVEL AS lvl
  FROM t
CONNECT BY s>PRIOR s AND LEVEL<cnt
)
,t3 AS (
SELECT id
      ,REGEXP_SUBSTR(s2,'[^,]+',1,rn) S3
  FROM t2
      ,(SELECT ROWNUM rn FROM DUAL CONNECT BY ROWNUM <=(SELECT MAX(cnt) FROM t))
WHERE t2.lvl>=rn     
)
SELECT t3.id
      ,RTRIM(REGEXP_REPLACE(WMSYS.WM_CONCAT(s3),'(,){2,}','\1'),',')
      ,RTRIM(REGEXP_REPLACE(WMSYS.WM_CONCAT(CASE WHEN s3 IS NULL THEN s END),'(,){2,}','\1'),',')
  FROM t3 PARTITION BY (id)
       RIGHT JOIN t
       ON t3.s3 = t.s
GROUP BY id;


-----------------------
同上，用xmlagg拼接：

WITH t AS (
SELECT REGEXP_SUBSTR(:s,'[^,]+',1,ROWNUM) s,COUNT(*) OVER() cnt
  FROM DUAL
CONNECT BY ROWNUM<=LENGTH(:s)-LENGTH(REPLACE(:s,','))+1
)
,t2 AS (
SELECT ROWNUM AS id, SYS_CONNECT_BY_PATH(s,',') AS s2, LEVEL AS lvl
  FROM t
CONNECT BY s>PRIOR s AND LEVEL<cnt
)
,t3 AS (
SELECT id
      ,REGEXP_SUBSTR(s2,'[^,]+',1,rn) S3
  FROM t2
      ,(SELECT ROWNUM rn FROM DUAL CONNECT BY ROWNUM <=(SELECT MAX(cnt) FROM t))
WHERE t2.lvl>=rn     
)
SELECT t3.id
      ,RTRIM(REGEXP_REPLACE(xmlagg(xmlelement (elmt,s3||',') order by s3).extract ('//text()'),'(,){2,}','\1'),',')
      ,RTRIM(REGEXP_REPLACE(xmlagg(xmlelement (elmt,(CASE WHEN s3 IS NULL THEN s END)||',') order by s).extract ('//text()'),'(,){2,}','\1'),',')
  FROM t3 PARTITION BY (id)
       RIGHT JOIN t
       ON t3.s3 = t.s
GROUP BY id;
-----------------------
列转行，用MINUS求补集，用CONNECT BY拼接：

WITH t AS (
SELECT REGEXP_SUBSTR(:s,'[^,]+',1,ROWNUM) s,COUNT(*) OVER() cnt
  FROM DUAL
CONNECT BY ROWNUM<=LENGTH(:s)-LENGTH(REPLACE(:s,','))+1
)
,t2 AS (
SELECT ROWNUM AS id, SUBSTR(SYS_CONNECT_BY_PATH(s,','),2) AS s2, LEVEL AS lvl
  FROM t
CONNECT BY s>PRIOR s AND LEVEL<cnt
)
,t3 AS (
SELECT id
      ,REGEXP_SUBSTR(s2,'[^,]+',1,rn) S3
  FROM t2
      ,(SELECT ROWNUM rn FROM DUAL CONNECT BY ROWNUM <=(SELECT MAX(cnt) FROM t))
WHERE t2.lvl>=rn     
)
,t4 AS (
SELECT id,MAX(SUBSTR(SYS_CONNECT_BY_PATH(s,','),2)) AS s4
  FROM (SELECT id,s,ROW_NUMBER() OVER(PARTITION BY id ORDER BY s) rn
          FROM (SELECT t2.id,t.s
                  FROM t,t2
                MINUS
                select id,s3 from t3
               )
       )
START WITH rn=1 CONNECT BY id = PRIOR id AND rn=PRIOR rn+1
GROUP BY id
)
select s2,s4
  FROM t2,t4
WHERE t2.id = t4.id;

casper0511

好题目 体会高手的思路