百度360必应搜狗淘宝本站头条
nodejsbufferorg.jsoupsetnocountoncv2.bitwise_nottime.strptime
当前位置：网站首页 > 编程字典 > 正文

选读SQL经典实例笔记14_层次查询（sql层级关系函数）

toyiye 2024-07-16 05:39 9 浏览 0 评论

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

相关推荐

为何越来越多的编程语言使用JSON（为什么编程）

JSON是JavascriptObjectNotation的缩写，意思是Javascript对象表示法，是一种易于人类阅读和对编程友好的文本数据传递方法，是JavaScript语言规范定义的一个子...

何时在数据库中使用 JSON（数据库用json格式存储）

在本文中，您将了解何时应考虑将JSON数据类型添加到表中以及何时应避免使用它们。每天?分享?最新?软件?开发?，Devops，敏捷?，测试?以及?项目?管理?最新?，最热门?的?文章?，每天?花?...

MySQL 从零开始:05 数据类型（mysql数据类型有哪些,并举例）

前面的讲解中已经接触到了表的创建，表的创建是对字段的声明，比如：上述语句声明了字段的名称、类型、所占空间、默认值和是否可以为空等信息。其中的int、varchar、char和decimal都...

JSON对象花样进阶（json格式对象）

一、引言在现代Web开发中，JSON（JavaScriptObjectNotation）已经成为数据交换的标准格式。无论是从前端向后端发送数据，还是从后端接收数据，JSON都是不可或缺的一部分。...

深入理解 JSON 和 Form-data（json和formdata提交区别）

在讨论现代网络开发与API设计的语境下，理解客户端和服务器间如何有效且可靠地交换数据变得尤为关键。这里，特别值得关注的是两种主流数据格式：...

JSON 语法（json 语法 priority）

JSON语法是JavaScript语法的子集。JSON语法规则JSON语法是JavaScript对象表示法语法的子集。数据在名称/值对中数据由逗号分隔花括号保存对象方括号保存数组JS...

JSON语法详解（json的语法规则）

JSON语法规则JSON语法是JavaScript对象表示法语法的子集。数据在名称/值对中数据由逗号分隔大括号保存对象中括号保存数组注意：json的key是字符串,且必须是双引号，不能是单引号...

MySQL JSON数据类型操作（mysql的json）

概述mysql自5.7.8版本开始，就支持了json结构的数据存储和查询，这表明了mysql也在不断的学习和增加nosql数据库的有点。但mysql毕竟是关系型数据库，在处理json这种非结构化的数据...

JSON的数据模式（json数据格式示例）

像XML模式一样，JSON数据格式也有Schema，这是一个基于JSON格式的规范。JSON模式也以JSON格式编写。它用于验证JSON数据。JSON模式示例以下代码显示了基本的JSON模式。{"...

前端学习——JSON格式详解（后端json格式）

JSON(JavaScriptObjectNotation)是一种轻量级的数据交换格式。易于人阅读和编写。同时也易于机器解析和生成。它基于JavaScriptProgrammingLa...

什么是 JSON:详解 JSON 及其优势（什么叫json）

现在程序员还有谁不知道JSON吗？无论对于前端还是后端，JSON都是一种常见的数据格式。那么JSON到底是什么呢？JSON的定义...

PostgreSQL JSON 类型:处理结构化数据

PostgreSQL提供JSON类型，以存储结构化数据。JSON是一种开放的数据格式，可用于存储各种类型的值。什么是JSON类型？JSON类型表示JSON(JavaScriptO...

JavaScript:JSON、三种包装类（javascript 包）

JOSN:我们希望可以将一个对象在不同的语言中进行传递，以达到通信的目的，最佳方式就是将一个对象转换为字符串的形式JSON（JavaScriptObjectNotation）-JS的对象表示法...

Python数据分析 只要1分钟 教你玩转JSON 全程干货

Json简介：Json，全名JavaScriptObjectNotation,JSON(JavaScriptObjectNotation(记号、标记))是一种轻量级的数据交换格式。它基于J...

比较一下JSON与XML两种数据格式?（json和xml哪个好）

JSON（JavaScriptObjectNotation）和XML（eXtensibleMarkupLanguage）是在日常开发中比较常用的两种数据格式，它们主要的作用就是用来进行数据的传...

取消回复欢迎 你 发表评论:

请填写验证码
一周热门
最近发表
标签列表