Oracle11R2 With as的递归算法

问题来源： 

PUZZLEUP 2017
http://www.itpub.net/thread-2090903-1-1.html
(出处: ITPUB论坛－中国最专业的IT技术社区)

中

#1
LOTTERY

In a lottery, every week 5 different numbers are randomly drawn from numbers between 1 and 30 (including 1 and 30). What is the probability of the 3 smallest numbers drawn this week being the same with the 3 smallest numbers drawn the previous week? 

Enter your answer as a reduced fraction. 
Example: 123/4567


在彩票中，每周从1到30（包括1和30）之间的数字随机抽取5个不同的数字。本周抽到的3个最小数字与上周抽到的3个最小数字相同的概率是多少？ 

答案以简化分数输入，例如：123/4567

一种解答:

SQL> with t as (select level n from dual connect by level <= 30),
  2   s as (select t1.n n1,t2.n n2,t3.n n3,t4.n n4,t5.n n5
  3          from t t1,t t2,t t3,t t4,t t5
  4          where t1.n < t2.n
  5            and t2.n < t3.n
  6            and t3.n < t4.n
  7            and t4.n < t5.n),
  8   p as (select count(*) cnt
  9          from s s1,s s2
10         where s1.n1 = s2.n1
11           and s1.n2 = s2.n2
12           and s1.n3 = s2.n3 ),
13   r as (select p1.cnt    n,
14                p2.c*p2.c m
15          from p p1,(select count(*) c from s) p2 ),
16  q(m,n) as (
17        select m,n from r
18        union all
19        select greatest(m-n,n),least(m-n,n)
20          from q
21        where m<>n)
22  select r.n/q.m||'/'||r.m/q.m as res from q,r where q.m=q.n
23  /
RES
--------------------------------------------------------------------------------
391/498771

其中 

q(m,n) as (
17        select m,n from r
18        union all
19        select greatest(m-n,n)