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

jiqing1004

哈哈  等了半天大侠终于出手了
先好好看看学习下

newkid

OO的#9和我的BITAND方法基本一样；
dingjun123的#15和我的TRANSLATE方法也很相似，jiqing1004在#19修改后就基本上一样了；
kmpx的#18一下没看懂；
kingtsi的#20很新颖，他先求出半个集合及其补集，然后反过来再UNION. 这个level<=(select count(rn)/2 from t)有时候能够提高很多效率的。

但是他的求补集方法不够严密：
(select wm_concat(x) from t where instr(m.p,','||x||',')=0)
如果X包含两个以上元素，这个WHERE就不成立了。

ocpdba591的#23用随机数产生组合，是不严密的做法。

[ 本帖最后由 newkid 于 2010-4-10 02:56 编辑 ]

newkid

#18有很多重复的子查询，简化后变成这样：

with tmp as (select 'a,b,c' col from dual)
,t2 AS (select substr(col,rownum,1) col,rownum rn from (select replace(col,',') col from tmp) connect by rownum<=length(col))
,t3 AS (select distinct decode(a.col,b.col,a.col,c.col,a.col,a.col||decode(b.col,c.col,b.col,b.col||c.col)) col
        from t2 a,
             t2 b,
             t2 c
      where a.rn<=b.rn and a.rn<=c.rn and b.rn<=c.rn)
select decode(length(a.col),1,a.col,substr(a.col,0,1)||','||substr(a.col,2,1)) col,
       decode(length(b.col),1,b.col,substr(b.col,0,1)||','||substr(b.col,2,1)) col1
from t3 a,
     t3 b
where a.col not like '%'||b.col||'%' and b.col not like '%'||a.col||'%'
and length(a.col)<>length(b.col) and length(a.col)<>3 and length(b.col)<>3
;

这个t3的写法，最后加逗号的写法，求补集的写法，都只能针对三个元素，是非常不灵活的。

原题目链接：
http://asktom.oracle.com/pls/ask ... 2295214400346237735

里面用的是BITAND解法。

ocpdba591

kingtsi

谢谢newkid大侠29#给出的方法还有32#的点评

32#你提到的
(select wm_concat(x) from t where instr(m.p,','||x||',')=0)
如果X包含两个以上元素，这个WHERE就不成立了。
可能是我没理解通还是，有点没看懂，split原始字符串后得到的每个元素分别存于t表列x中的每一行，只会有一个元素，不知道newkid大侠的意思是，我是不是理解错了你的意思？请指点，谢谢 ^_^

CaptainKobe

准备抢注各种域名

asknewkid.net
asknewkid.com
asknewkid.com.cn
asknewkid.cn

newkid

原帖由 kingtsi 于 2010-4-10 10:11 发表
谢谢newkid大侠29#给出的方法还有32#的点评

32#你提到的
(select wm_concat(x) from t where instr(m.p,','||x||',')=0)
如果X包含两个以上元素，这个WHERE就不成立了。
可能是我没理解通还是，有点没看懂，split原始字符串后得到的每个元素分别存于t表列x中的每一行，只会有一个元素，不知道newkid大侠的意思是，我是不是理解错了你的意思？请指点，谢谢 ^_^

哦，我看错了，确实X是单元素。
你的方法非常好！

借鉴一下：
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
      ,(SELECT WMSYS.WM_CONCAT(t.s) FROM t WHERE INSTR(','||s2||',',t.s)=0 ) AS COL2 ---- kingtsi的方法
  FROM t2;

[ 本帖最后由 newkid 于 2010-4-11 03:08 编辑 ]

newkid

原帖由 CaptainKobe 于 2010-4-10 10:37 发表
准备抢注各种域名

asknewkid.net
asknewkid.com
asknewkid.com.cn
asknewkid.cn

咱还是有自知之明的! 就是一个老程序员在发挥余热而已。

k68

先好好看看学习下

newkid

终于把11GR2折腾好了,第一件事就是玩玩这个递归的WITH子查询:

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


WITH t AS (
SELECT REGEXP_SUBSTR(:s,'[^,]+',1,ROWNUM) s
  FROM DUAL
CONNECT BY ROWNUM<=LENGTH(:s)-LENGTH(REPLACE(:s,','))+1
)
, T2(s,s1,s2) AS (
  SELECT s,s s1, REPLACE(','||:s||',',','||s||',',',') as s2
    FROM t
  UNION ALL
  SELECT t.s,t2.s1||','||t.s,REPLACE(','||t2.s2||',',','||t.s||',',',') as s2
    FROM t, T2
  WHERE t2.s<t.s
)
SELECT s1,TRIM(BOTH ',' FROM s2) FROM t2
WHERE TRIM(BOTH ',' FROM s2) IS NOT NULL;

它提供了逐层控制的功能，用REPLACE很方便地把新加入的字符从补集中剔除。