9 14
10 14
11 14
12 14
13 14
14 14
14 rows selected.
递归WITH写法:
WITH t(n,cnt) AS (
SELECT 1,COUNT(*) cnt FROM EMP --- 先构造第一个
UNION ALL
SELECT t.n+1 ---- 递增1
,t.cnt ---- 这个cnt列不做任何修改,从第一层得来
FROM t ---- 没有任何连接,因为不需要
WHERE t.n<t.cnt ---- 在这里看到cnt的作用,就是用于终止遍历
)
SELECT * FROM t;
结果同上(略)。
例3:
独立事件的排列组合:一个布袋中装有数量相同的四种颜色的小球。随机从布袋中取四次,每次取完都放回去。现在问四次结
果总颜色数等于3的概率是多少?
传统的CONNECT BY写法:
WITH t AS (
SELECT ROWNUM rn -- 先构造一个1,2,3,4的结果集,每个rn表示一种颜色
FROM DUAL
CONNECT BY ROWNUM<=4
)
,t2 AS ( ---- 集合t2模拟独立取四次的动作,最终结果会有4*4*4*4=256行
SELECT ROWNUM id ---- 构造唯一ID供下面拆分用
,REPLACE(SYS_CONNECT_BY_PATH(rn,'@'),'@') path ---- 用一个特殊字符@来作分隔符, 并在最后用REPLACE把它去除
,COUNT(*) OVER() cnt ---- 利用分析函数算出总行数并把它作为一个列返回
FROM t ---- 这个是有四行的集合
WHERE LEVEL=4 ---- 我们需要的仅仅是最后一层的结果。在PATH里面已经包含了取四次的所有结果组合
CONNECT BY LEVEL<=4 ---- 没有任何条件,前后都是独立的
)
,t3 AS ( ---- 集合t3把t2中的PATH包含的颜色组合拆开为四行
SELECT id,cnt,SUBSTR(PATH,rn,1) color
FROM t2,t ---- 笛卡儿积,用于把t2中的一行变为四行
)
SELECT COUNT(COUNT(*))/MAX(cnt) AS prob
FROM t3
GROUP BY id,cnt
HAVING COUNT(DISTINCT color)=3 --- 每一个id中包含三种颜色
;
结果:
PROB
----------
.5625
这个例子展示了CONNECT BY来模拟排列组合的技巧。每一层遍历表示一次抽取的动作,因为每次都是完全独立的,在
CONNECT BY 里面仅仅限制了抽取次数(遍历层数)而没有其他条件。SYS_CONNECT_BY_PATH可以把截至当前为止所
访问到的各层次的数据串起来,在LEVEL=N就包含了前N层的排列组合情况。你可以用这个查询来看看中间生成的结果集t2:
WITH t AS (
SELECT ROWNUM rn -- 先构造一个1,2,3,4的结果集,每个rn表示一种颜色
FROM DUAL
CONNECT BY ROWNUM<=4
)
,t2 AS ( ---- 集合t2模拟独立取四次的动作,最终结果会有4*4*4*4=256行
SELECT ROWNUM id ---- 构造唯一ID供下面拆分用
,REPLACE(SYS_CONNECT_BY_PATH(rn,'@'),'@') path ---- 用一个特殊字符@来作分隔符, 并在最后用REPLACE把它去除
,COUNT(*) OVER() cnt ---- 利用分析函数算出总行数并把它作为一个列返回
FROM t ---- 这个是有四行的集合
WHERE LEVEL=4 ---- 我们需要的仅仅是最后一层的结果。在PATH里面已经包含了取四次的所有结果组合
CONNECT BY LEVEL<=4 ---- 没有任何条件,前后都是独立的
)
SELECT * FROM t2;