SQL Cookbook 系列 - 分层查询

1. 表示父-子关系
2. 表示子-父-祖关系
3. 创建表的分层视图
4. 为给定父行找到所有的子行
5. 确定哪些行是叶节点、分支节点及根节点

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