学步园

//数据：<br /> id num price<br /> 1 1 5<br /> 2 2 14<br /> 3 1 6<br /> 4 4 20<br /> //结果:<br /> id num price<br /> 1 1 5<br /> 2 1 7<br /> 3 1 7<br /> 4 1 6<br /> 5 1 5<br /> 6 1 5<br /> 7 1 5<br /> 8 1 5<br /> //解法：<br /> with a as(<br /> select 1 id,1 num,5 price from dual union all<br /> select 2,2,14 from dual union all<br /> select 3,1,6 from dual union all<br /> select 4,4,20 from dual)<br /> , b as(<br /> select 1 num, rownum rn<br /> from (select max(num) x from a)<br /> connect by rownum <= x)<br /> select b.num, a.id, a.price / a.num price,b.rn<br /> from a, b<br /> where a.num >= b.rn<br /> /<br /> select rownum id, ta.*<br /> from(<br /> select b.num, a.price / a.num price<br /> from a, b<br /> where a.num >= b.rn--<br /> order by a.id, b.rn) ta<br /> /这里为什么要取出max_num呢？<br /> select max(num) from a<br /> MAX(NUM)<br /> ----------<br /> 4<br /> //至于connect by递归查询，还有很多不明白，比如下面连个查询：<br /> SQL> select 1,rownum from dual connect by rownum<=4;<br /> 1 ROWNUM<br /> ---------- ----------<br /> 1 1<br /> 1 2<br /> 1 3<br /> 1 4<br /> 1 5<br /> SQL> select 1,level from dual connect by level<=4;<br /> 1 LEVEL<br /> ---------- ----------<br /> 1 1<br /> 1 2<br /> 1 3<br /> 1 4<br /> //<br /> select 1 num, rownum rn<br /> from (select max(num) x from a)<br /> connect by rownum <= x<br /> NUM RN<br /> ---------- ----------<br /> 1 1<br /> 1 2<br /> 1 3<br /> 1 4<br /> 1 5<br /> /<br /> //这是一个获得a,b表的笛卡尔积(A×A)的查询，正如我们知道，它将返回20行<br /> select b.num, a.price / a.num price<br /> from a, b<br /> //当我们加了一个条件筛选之后会是怎么样的呢？<br /> //where a.num >= b.rn这个条件限制了返回的结果集，<br /> select b.num, a.id, a.price / a.num price,b.rn<br /> from a, b<br /> where a.num >= b.rn;<br /> NUM ID PRICE RN<br /> ---------- ---------- ---------- ----------<br /> 1 1 5 1<br /> 1 2 7 1<br /> 1 3 6 1<br /> 1 4 5 1<br /> 1 2 7 2<br /> 1 4 5 2<br /> 1 4 5 3<br /> 1 4 5 4<br /> //从结果集中，我们看出，a表将所以的记录与b表中的每一条记录一遍一遍的比较；<br /> //对于b表中第一条数据，a表中所有的行的num都大于它，所以这里返回了rn=1对应的4条记录；<br /> //b表中rn=2，a表中只有id=2,4的数据的num大于它，所以price只返回7,5；<br /> //b表中rn=3，a表中只有id=4的这条数据的num大于它，所以price返回5；<br /> //b表中rn=4，a表中只有id=4的这条数据的num与之对应，所以price=5<br /> //上面的结果就是这么来的；<br /> // 

原帖：http://topic.csdn.net/u/20110607/00/60fef66a-d97a-4d1b-9642-594b77370559.html