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也已支持
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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/8520577/viewspace-1062114/