1.先建一个树形表:
create
table tst
(
id integer ,
parentId int ,
name varchar ( 20 ))
id integer ,
parentId int ,
name varchar ( 20 ))
2.插入数据
insert
into
tst
values
( 1 , 0 , ' a ' ),
( 2 , 0 , ' b ' ),
( 3 , 1 , ' c ' ),
( 4 , 1 , ' d ' ),
( 5 , 4 , ' d ' ),
( 6 , 5 , ' d ' )
3.使用递归查询
( 1 , 0 , ' a ' ),
( 2 , 0 , ' b ' ),
( 3 , 1 , ' c ' ),
( 4 , 1 , ' d ' ),
( 5 , 4 , ' d ' ),
( 6 , 5 , ' d ' )
with
rpl (id,parentId,name)
as
(
select id,parentId,name from tst where parentId = 1
union all
select child.id,child.parentId,child.name from rpl parent, tst child where parent.id = child.parentId
)
select * from rpl
(
select id,parentId,name from tst where parentId = 1
union all
select child.id,child.parentId,child.name from rpl parent, tst child where parent.id = child.parentId
)
select * from rpl
这个语句在在db2 7中就有了,在sql2005中才出现.