create table test_with(
 pid int,
 parentid int,
 name varchar(60)
)




insert into test_with values(1, null, 'A')
insert into test_with values(2, null, 'B')
insert into test_with values(3, null, 'C')


insert into test_with values(4, 1, 'A1')
insert into test_with values(5, 1, 'A2')
insert into test_with values(6, 1, 'A3')


update test_with set name = 'A2' where pid = 5
update test_with set name = 'A3' where pid = 6


select * from test_with


insert into test_with values(7, 4, 'A11')
insert into test_with values(8, 5, 'A22')
insert into test_with values(9, 6, 'A33')




with t(pid, parentid, name, level) as (
  select pid, parentid, name, 0 as level from test_with where parentid is null
  and pid = 1
  union all 
  select test_with.pid, test_with.parentid, test_with.name, level+1 from t , test_with 
  where t.pid = test_with.parentid and test_with.parentid is not null
  and t.pid <> test_with.pid
) 
select * from t where level <= 3 OPTION(MAXRECURSION 5000)


with t(pid, parentid, name,parentname, level) as (
  select pid, parentid, name, cast(null as varchar(60)) as parentname,0 as level from test_with where parentid is null
  union all 
  select test_with.pid, test_with.parentid, test_with.name , t.name as parentname, level+1 from t , test_with 
  where t.pid = test_with.parentid 
) 
select * from t where level <= 3 OPTION(MAXRECURSION 5000)



可以用来模拟start with..connect by

在test_with.pid上纠结了许久，不小心写成t.pid,结果造成无限的递归出错:

消息 530，级别 16，状态 1，第 1 行
语句被终止。完成执行语句前已用完最大递归 5000。


这个特性在Oracle 11g, 12c也已支持