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 where depth=2;

oracle:

select ltrim(sys_connect_by_path(ename,'-->'),'-->') leaf_branch_root

from emp where level=3

start with ename='Will' connect by prior mgr=empno;

mysql/postgresql:

select a.ename||'-->'||b.ename||'-->'||c.ename as leaf_branch_root

from emp a,emp b, emp c

where a.ename='Will' and a.mgr=b.empno and b.mgr=c.empno;

Note:单表的多级连接

 

3.创建表的分层视图

db2/sqlserver:

with x (ename,empno) as (

select cast(ename as varchar(100)),empno

from emp where mgr is null

union all

select cast(x.ename||' - '||e.ename as varchar(100)),e.empno

from emp e,x where e.mgr=x.empno

)

select ename as emp_tree from x order by 1;

oracle:

select ltrim(sys_connenct_by_path(ename,' - '),' - ') emp_tree

from emp

start with mgr is null connect by prior empno=mgr order by 1;

postgresql:

select emp_tree from (

select ename as emp_tree

from emp where mgr is null

union

select a.ename||' - '||b.ename

from emp a join emp b on (a.empno=b.mgr)

where a.mgr is null

union

select rtrim(a.ename||' - '||b.ename||' - '||c.ename,' - ')

from emp a join emp b on (a.empno=b.mgr)

left join emp c on (b.empno=c.mgr)

where a.ename='Root'

union

select rtrim(a.ename||' - '||b.ename||' - '||c.ename||' - '||d.ename,' - ')

from emp a join emp b on (a.empno=b.mgr)

join emp c on (b.empno=c.mgr)

left join emp d on (c.empno=d.mgr)

where a.ename='Root'

) x where tree is not null order by 1;

mysql:

select emp_tree from (

select ename as emp_tree

from emp where mgr is null

union

select concat(a.ename,' - ',b.ename)

from emp a join emp b on (a.empno=b.mgr)

where a.mgr is null

union

select concat(a.ename,' - ',b.ename,' - ',c.ename)

from emp a join emp b on (a.empno=b.mgr)

left join emp c on (b.empno=c.mgr)

where a.ename='Root'

union

select concat(a.ename,' - ',b.ename,' - ',c.ename,' - ',d.ename)

from emp a join emp b on (a.empno=b.mgr)

join emp c on (b.empno=c.mgr)

left join emp d on (c.empno=d.mgr)

where a.ename='Root'

) x where tree is not null order by 1;

Note:因为层数不确定，在mysql和postgresql中操作很麻烦。

 

4.为给定父行找到所有的子行

db2/sqlserver:

with x(ename,empno) as (

select ename,empno from emp where ename='Will'

union all

select e.ename,e.empno from emp e,x where x.empno=e.mgr

)

select ename from x;

oracle:

select ename from emp start with ename='Will'

connect by prior empno=mgr;

mysql/postgresql:

多个查询连接之后在回找

Note：如果不能像oracle，自动进行关联，那么关系之中的关键关系值就显得非常重要

 

5.确定哪些行是叶节点、分支节点及根节点

db2/mysql/postgresql/sqlserver:

select e.ename,

(select sign(count(*)) from emp d

where 0=(select count(*) from emp f

where f.mgr=e.empno)) as is_leaf,

(select sign(count(*)) from emp d

where d.mgr=e.empno and e.mgr is not null) as is_branch,

(select sign(count(*)) from emp d

where d.empno=e.empno and d.mgr is null) as is_root

from emp e;

oracle:

select ename,connect_by_isleaf is_leaf,

(select count(*) from emp e

where e.mgr=a.empno

and a.mgr is not null

and rownum=1) is_branch,

decode(ename,connect_by_root(ename),1,0) is_root

from emp a start with mgr is null

connect by prior empno=mgr order by 4 desc,3 desc;

Note：标量子查询的应用