oracle 查询结果树形,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)对应的叶子。以上的例子，结果为：

1

1

2

1,2

3

1,2,3

4

1,2,3,4

4

1,2,4

3

1,3

4

1,3,4

4

1,4

2

2

3

2,3

4

2,3,4

4

2,4

3

3

4

3,4

4

4

这个就是我们想要的结果。而且也可以看出，它是呈收缩趋势的。对应最终的子集数可以由如下公式确定：

∑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