选读SQL经典实例笔记14_层次查询-Toy模板网

这篇具有很好参考价值的文章主要介绍了选读SQL经典实例笔记14_层次查询。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

选读SQL经典实例笔记14_层次查询文章来源地址https://www.toymoban.com/news/detail-608693.html

1. 结果集

1.1. sql

select empno,mgr
  from emp
order 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 JONES
SCOTT works for JONES
JAMES works for BLAKE
TURNER works for BLAKE
MARTIN works for BLAKE
WARD works for BLAKE
ALLEN works for BLAKE
MILLER works for CLARK
ADAMS works for SCOTT
CLARK works for KING
BLAKE works for KING
JONES works for KING
SMITH 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       7839
KING             7839
MILLER           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 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

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 = 'MILLER
connect 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
-------------------------------
KING
KING - BLAKE
KING - BLAKE - ALLEN
KING - BLAKE - JAMES
KING - BLAKE - MARTIN
KING - BLAKE - TURNER
KING - BLAKE - WARD
KING - CLARK
KING - CLARK - MILLER
KING - JONES
KING - JONES - FORD
KING - JONES - FORD - SMITH
KING - JONES - SCOTT
KING - 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 null
connect 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'
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 = '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
 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 = 'KING'
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 = 'KING'
       ) x
 where tree is not null
 order by 1

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

5.1. 结果集

5.1.1. sql

ENAME
---------
JONES
SCOTT
ADAMS
FORD
SMITH

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 v1
as
select ename,mgr,empno
  from emp
 where ename = 'JONES'

create view v2
as
select ename,mgr,empno
  from emp
 where mgr = (select empno from v1)

create view v3
as
select ename,mgr,empno
  from emp
 where mgr in (select empno from v2)

5.8.2. sql

select ename from v1
 union
select ename from v2
 union
select ename from v3

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

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

6.1. 结果集

6.1.1. sql

ENAME         IS_LEAF  IS_BRANCH    IS_ROOT
---------- ---------- ---------- ----------
KING                 0         0          1
JONES                0         1          0
SCOTT                0         1          0
FORD                 0         1          0
CLARK                0         1          0
BLAKE                0         1          0
ADAMS                1         0          0
MILLER               1         0          0
JAMES                1         0          0
TURNER               1         0          0
ALLEN                1         0          0
WARD                 1         0          0
MARTIN               1         0          0
SMITH                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 null
connect by prior empno = mgr
order by 4 desc, 3 desc

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

到了这里，关于选读SQL经典实例笔记14_层次查询的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！

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