有个特殊的案例,如下:
给出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