手机

英国房地产因利率上升陷入困境房价正以2011年来最快速度下跌

宁夏评选出上半年10名“宁夏好人” 95后消防员因敬业奉献入选

家电

选读SQL经典实例笔记14_层次查询

2023-07-28 07:07:32 来源：博客园

【资料图】

1.结果集

1.1.sql

select empno,mgr  from emporder by 2    EMPNO        MGR---------- ----------      7788       7566      7902       7566      7499       7698      7521       7698      7900       7698      7844       7698      7654       7698      7934       7782      7876       7788      7566       7839      7782       7839      7698       7839      7369       7902      7839

2.展现父子关系

2.1.结果集

2.1.1.sql

EMPS_AND_MGRS------------------------------FORD works for JONESSCOTT works for JONESJAMES works for BLAKETURNER works for BLAKEMARTIN works for BLAKEWARD works for BLAKEALLEN works for BLAKEMILLER works for CLARKADAMS works for SCOTTCLARK works for KINGBLAKE works for KINGJONES works for KINGSMITH works for FORD

2.2.DB2

2.3.Oracle

2.4.PostgreSQL

2.5.自连接EMP表

2.5.1.sql

select a.ename || " works for " || b.ename as emps_and_mgrs  from emp a, emp b where a.mgr = b.empno

2.6.MySQL

2.6.1.CONCAT函数连接字符串

2.6.1.1.sql

select concat(a.ename, " works for ",b.ename) as emps_and_mgrs  from emp a, emp b where a.mgr = b.empno

2.7.SQL Server

2.7.1.加号“+”连接字符串

2.7.1.1.sql

select a.ename + " works for " + b.ename as emps_and_mgrs  from emp a, emp b where a.mgr = b.empno

3.展现祖孙关系

3.1.结果集

3.1.1.sql

select ename,empno,mgr  from emp where ename in ("KING","CLARK","MILLER")ENAME           EMPNO        MGR---------- ---------- ----------CLARK            7782       7839KING             7839MILLER           7934       7782

3.1.2.sql

LEAF___BRANCH_ _ _ROOT----------------------MILLER--＞CLARK--＞KING

3.2.DB2

3.3.SQL Server

3.4.WITH递归查询

3.4.1.sql

with x (tree,mgr,depth)    as (select cast(ename as varchar(100)),        mgr, 0  from emp where ename = "MILLER"union allselect 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

3.4.2.SQL Server的字符串连接操作符+

3.4.3.DB2的字符串连接操作符||

3.5.Oracle

3.5.1.SYS_CONNECT_BY_PATH函数

3.5.1.1.sql

select ltrim(          sys_connect_by_path(ename,"--＞"),        "--＞") leaf___branch___root   from emp  where level = 3  start with ename = "MILLERconnect by prior mgr = empno

3.6.PostgreSQL

3.7.MySQL

3.8.自连接两次

3.8.1.sql

select a.ename||"--＞"||b.ename              ||"--＞"||c.ename as leaf___branch___root  from emp a, emp b, emp c where a.ename = "MILLER"   and a.mgr = b.empno   and b.mgr = c.empno

3.8.2.MySQL使用CONCAT函数

4.创建层次视图

4.1.结果集

4.1.1.sql

EMP_TREE-------------------------------KINGKING - BLAKEKING - BLAKE - ALLENKING - BLAKE - JAMESKING - BLAKE - MARTINKING - BLAKE - TURNERKING - BLAKE - WARDKING - CLARKKING - CLARK - MILLERKING - JONESKING - JONES - FORDKING - JONES - FORD - SMITHKING - JONES - SCOTTKING - JONES - SCOTT – ADAMS

4.2.DB2

4.3.SQL Server

4.4.WITH递归查询

4.4.1.sql

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

4.4.2.SQL Server使用字符串连接操作符 +

4.5.Oracle

4.5.1.CONNECT BY函数

4.5.1.1.sql

select ltrim(          sys_connect_by_path(ename," - "),        " - ") emp_tree   from emp  start with mgr is nullconnect by prior empno=mgr  order by 1

4.6.PostgreSQL

4.6.1.sql

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 = "KING"unionselect 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 = "KING"       ) x where tree is not null order by 1

4.7.MySQL

4.7.1.sql

select emp_tree   from ( select ename as emp_tree   from emp  where mgr is null unionselect concat(a.ename," - ",b.ename)  from emp a       join       emp b on (a.empno=b.mgr) where a.mgr is nullunionselect 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 = "KING"unionselect 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 = "KING"       ) x where tree is not null order by 1

5.给定的父节点对应的所有子节点

5.1.结果集

5.1.1.sql

ENAME---------JONESSCOTTADAMSFORDSMITH

5.2.DB2

5.3.SQL Server

5.4.WITH递归查询

5.4.1.sql

with x (ename,empno)      as (  select ename,empno    from emp   where ename = "JONES"   union all  select e.ename, e.empno    from emp e, x   where x.empno = e.mgr  )  select ename    from x

5.5.Oracle

5.5.1.CONNECT BY子句

5.5.1.1.sql

select ename   from emp  start with ename = "JONES"connect by prior empno = mgr

5.6.PostgreSQL

5.7.MySQL

5.8.自连接

5.8.1.sql

create view v1asselect ename,mgr,empno  from emp where ename = "JONES"create view v2asselect ename,mgr,empno  from emp where mgr = (select empno from v1)create view v3asselect ename,mgr,empno  from emp where mgr in (select empno from v2)

5.8.2.sql

select ename from v1 unionselect ename from v2 unionselect ename from v3

5.8.3.需要提前知道层次关系的深度

6.确认叶子节点、分支节点和根节点

6.1.结果集

6.1.1.sql

ENAME         IS_LEAF  IS_BRANCH    IS_ROOT---------- ---------- ---------- ----------KING                 0         0          1JONES                0         1          0SCOTT                0         1          0FORD                 0         1          0CLARK                0         1          0BLAKE                0         1          0ADAMS                1         0          0MILLER               1         0          0JAMES                1         0          0TURNER               1         0          0ALLEN                1         0          0WARD                 1         0          0MARTIN               1         0          0SMITH                1         0          0

6.2.DB2

6.3.PostgreSQL

6.4.MySQL

6.5.SQL Server

6.6.3个标量子查询

6.6.1.sql

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 order by 4 desc,3 desc

6.7.Oracle

6.7.1.sql

select ename,        connect_by_isleaf is_leaf,        (select count(*) from emp e          where e.mgr = emp.empno            and emp.mgr is not null            and rownum = 1) is_branch,        decode(ename,connect_by_root(ename),1,0) is_root   from emp  start with mgr is nullconnect by prior empno = mgrorder by 4 desc, 3 desc

6.7.1.1.Oracle Database 10g新增的CONNECT_BY_ROOT和CONNECT_BY_ISLEAF

关键词：