SQL Server遞迴查詢

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也已支援

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8520577/viewspace-1062114/，如需轉載，請註明出處，否則將追究法律責任。