较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
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