昨天看https://jonathanlewis.wordpress.com/2014/02/16/recursive-subquery-factoring/,发现sql语句可以使用递归写法,我还第1
次知道sql能这么写.自己记录一些例子.
with data(p) as (
select 1 p from dual
union all
select p + 1 from data where p )
select p
from data
where rownum ;
P
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
--这是一种递归的写法.
--自己做一个斐波那契数列.1,1,2,3,5,8,13,21,34,55,89...看看.
with data(a,b) as (
select 1 a,1 b from dual
union all
select b a, a+b b from data where a+b )
select a
from data
where rownum ;
A
----------
1
1
2
3
5
8
13
21
34
55
10 rows selected.
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID cbdzqzz4b73y6, child number 0
-------------------------------------
with data(a,b) as ( select 1 a,1 b from dual union all
select b a, a+b b from data where a+b where rownum Plan hash value: 37253879
---------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 4 (100)|
|* 1 | COUNT STOPKEY | | | |
| 2 | VIEW | | 2 | 4 (0)|
| 3 | UNION ALL (RECURSIVE WITH) BREADTH FIRST| | | |
| 4 | FAST DUAL | | 1 | 2 (0)|
| 5 | RECURSIVE WITH PUMP | | | |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM
