Oracle 樹形結構查詢的特殊用法

有個特殊的案例，如下：
給出A、B、C三個元素，求出這三個元素對應的所有非空子集（含本集），且是順序無關的。問用SQL語句該如何實現？
首先，我們可以確認的是，這三個元素所組成的非空子集為：
A
A,B
A,B,C
A,C
B
B,C
C
這麼六個。
這個問題初看以為很簡單，用plsql的方式應該比較容易實現。但是用sql方式我想了好長時間都沒想出來合理的解決方法。這個問題也擱置了不少時間，後來有一次在itpub開發論壇上看到個樹形結構查詢connect by level <= [N]這個用法，看起來似乎能夠解決這個問題，因為它給出了所有的集合，但是是順序相關，且是有重複元素的：
SQL> with tmp as (select rownum n from dual connect by rownum < 4)
  2  select sys_connect_by_path(n, ',') t, n--, level n
  3    from tmp
  4  connect by level < 4
  5  /
 
T                                                                                         N
-------------------------------------------------------------------------------- ----------
,1                                                                                        1
,1,1                                                                                      1
,1,1,1                                                                                    1
,1,1,2                                                                                    2
,1,1,3                                                                                    3
,1,2                                                                                      2
,1,2,1                                                                                    1
,1,2,2                                                                                    2
,1,2,3                                                                                    3
,1,3                                                                                      3
,1,3,1                                                                                    1
,1,3,2                                                                                    2
,1,3,3                                                                                    3
,2                                                                                        2
,2,1                                                                                      1
,2,1,1                                                                                    1
,2,1,2                                                                                    2
,2,1,3                                                                                    3
,2,2                                                                                      2
,2,2,1                                                                                    1
,2,2,2                                                                                    2
,2,2,3                                                                                    3
,2,3                                                                                      3
,2,3,1                                                                                    1
,2,3,2                                                                                    2
,2,3,3                                                                                    3
,3                                                                                        3
,3,1                                                                                      1
,3,1,1                                                                                    1
,3,1,2                                                                                    2
,3,1,3                                                                                    3
,3,2                                                                                      2
,3,2,1                                                                                    1
,3,2,2                                                                                    2
,3,2,3                                                                                    3
,3,3                                                                                      3
,3,3,1                                                                                    1
,3,3,2                                                                                    2
,3,3,3                                                                                    3
 
39 rows selected
因此如何去除這些多餘的元素成了我後面煩惱的事情。甚至對於這種查詢結構我還做了一番總結：對於N個元素，結果會有N^1+N^2+...+N^N次個組合，即：
∑N^n(n=1..N)
因此假如有三個元素，則有3^1+3^2+3^3=3+9+27=39個組合。其基本原理是每個元素都可以作為集合中任意元素的子節點。如對於元素1，1、2、3都是他下面對應的子節點，於是對應1有
1
1,1
1,1,1
1,1,2
1,1,3
1,2
1,2,1
1,2,2
1,2,3
1,3
1,3,1
1,3,2
1,3,3
元素一多，組合會以非常大的趨勢膨脹。
因此對於以上結果並不是很滿意，所以也就沒繼續往下考慮。後來在論壇中其他高手的不斷跟帖中找到了一個很完美的解決方案，真是簡單而實用：
with tmp as (select rownum n from dual connect by rownum < 5)
 select sys_connect_by_path(n, ',') t, n--, level n
    from tmp
connect by n > prior n
/

 這個方案很好的給出了我們所要的答案。它的關鍵在於connect n > prior n的使用。對於此種方式，我一直都不甚了了。於是今天再回顧了一下層次查詢的基本概念。start with用於對樹枝的裁剪。而connect by用於指定遍歷的方向。由prior指定的一端向另一端遍歷。
因此上面的語句可以跟前面的connect by level <= N結合起來理解。如果沒有指定n > prior n，則集合中所有的元素都會成為集合中任一元素的葉子。指定了該遍歷方向，則集合中所有大於任一元素(A)的元素都會成為該元素(A)對應的葉子。以上的例子，結果為：

這個就是我們想要的結果。而且也可以看出，它是呈收縮趨勢的。對應最終的子集數可以由如下公式確定：
∑2^n(n=1..N)(其中N是指元素個數)

進一步，如果要求出補集該如何做呢？
如，對於集合1、2、3，有子集1、2，則它的補集即為：3。同樣itpub上高手給出了一個很巧妙的方法：
with a as
(select t,  n,
         row_number() over(partition by n order by t) s,
         count(1) over(partition by n) + 1 c
    from (select sys_connect_by_path(n, ',') t, level n
            from (select rownum n from dual connect by rownum < 4)
          connect by nocycle n > prior n
          )
)
SELECT ltrim (a.t, ','), ltrim(b.t, ',')
  from a, a b
 WHERE a.n + b.n = 4 - 1
   AND a.s + b.s = b.c