- 表示父-子关系
- 表示子-父-祖关系
- 创建表的分层视图
- 为给定父行找到所有的子行
- 确定哪些行是叶节点、分支节点及根节点
1.表示父-子关系
db2/oracle/postgresql:
select a.ename || ' works for ' || b.ename as info
from emp a,emp b where a.leaderno=b.empno;
mysql:
select concat(a.ename, ' works for ', b.ename) as info
from emp a,emp b where a.leaderno=b.empno;
sqlserver:
select a.ename + ' works for ' + b.ename as info
from emp a,emp b where a.leaderno=b.empno;
Note:最简答的单表自连接
2.表示子-父-祖关系
db2/sqlserver:
with x (tree,mgr,depth) as (
select cast(ename as varchar(100)),mgr,0
from emp where ename='Will'
union all
select cast(x.tree+'-->'+e.ename as varchar(100)),e.mgr,x.depth+1
from emp e,x where x.mgr=e.empno
)
select tree leaf_branch_root from x wh