db2查询树写法

hhnf333

较oracle来说，db2的查询树写法比较复杂，但是当你习惯以后，其实写法也挺简单的。
db2要自己写递归，没有提供相应的函数

create table tree
{
   treeid，
    treename，
    treesuperid
}

例子：
WITH  RPL (treesuperid, treeid, treename,level) AS
(
SELECT ROOT.treesuperid, ROOT.treeid, ROOT.treename,1 FROM tree ROOT where ROOT.treeid='120000'
UNION ALL
SELECT CHILD.treesuperid, CHILD.treeid, CHILD.treename,PARENT.level+1 FROM RPL PARENT, tree CHILD WHERE PARENT.treeid= CHILD.treesuperid
)
select RPL.treesuperid, RPL.treeid, RPL.treename from RPL WHERE level =2

这个sql是取的第二级树。

如果要取完整的树的话，用下面sql


WITH  RPL (treesuperid, treeid, treename) AS
(
SELECT ROOT.treesuperid, ROOT.treeid, ROOT.treename FROM tree ROOT where ROOT.treeid='120000'
UNION ALL
SELECT CHILD.treesuperid, CHILD.treeid, CHILD.treename FROM RPL PARENT, tree CHILD WHERE PARENT.treeid= CHILD.treesuperid
)
select RPL.treesuperid, RPL.treeid, RPL.treename from RPL