美文网首页
选读SQL经典实例笔记14_层次查询

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

作者: 躺柒 | 来源:发表于2023-07-27 06:50 被阅读0次
    选读SQL经典实例笔记14_层次查询.png

    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_层次查询

          本文链接:https://www.haomeiwen.com/subject/vgjhpdtx.html