当前位置：极客教程 > SQL > SQL 实例 > SQL 创建层次视图

SQL 创建层次视图

SQL 创建层次视图，你想用一个结果集展示一个表的全部数据之间的层次关系。比如 EMP 表，员工 KING 没有管理者，因此 KING 是根节点。你希望展示从 KING 开始，所有 KING 的下属以及 KING 的下属的下属（如果有的话）。

SQL 创建层次视图 问题描述

你想用一个结果集展示一个表的全部数据之间的层次关系。比如 EMP 表，员工 KING 没有管理者，因此 KING 是根节点。你希望展示从 KING 开始，所有 KING 的下属以及 KING 的下属的下属（如果有的话）。最终，你希望得到如下所示的结果集。

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

SQL 创建层次视图 解决方案

DB2 和 SQL Server
使用 WITH 递归查询从 KING 开始构造层次关系，进而最终展现出所有员工之间的关系。下面的解决方案使用了 DB2 的字符串连接操作符||。SQL Server 用户则需要使用字符串连接操作符 +。除了字符串连接操作符的不同，本解决方案同时适用于这两种数据库。

 1   with x (ename,empno)
 2      as (
 3  select cast(ename as varchar(100)),empno
 4    from emp
 5   where mgr is null
 6   union all
 7  select cast(x.ename||' - '||e.ename as varchar(100)),
 8         e.empno
 9    from emp e, x
10   where e.mgr = x.empno
11  )
12  select ename as emp_tree
13    from x
14   order by 1

Oracle
使用 CONNECT BY 函数定义层次关系，接着使用 SYS_CONNECT_BY_PATH 函数格式化输出结果。

1  select ltrim(
2           sys_connect_by_path(ename,' - '),
3         ' - ') emp_tree
4    from emp
5   start with mgr is null
6 connect by prior empno=mgr
7   order by 1

本解决方案不同于前一个实例，它不需要基于伪列 LEVEL 的过滤条件。因为没有该过滤条件，所有可能的树形结构都会被显示出来（此处 PRIOR EMPNO=MGR）。
PostgreSQL
使用 3 个 UNION 和多个自连接。

 1  select emp_tree
 2    from (
 3  select ename as emp_tree
 4    from emp
 5   where mgr is null
 6  union
 7  select a.ename||' - '||b.ename
 8    from emp a
 9         join
10         emp b on (a.empno=b.mgr)
11   where a.mgr is null
12  union
13  select rtrim(a.ename||' - '||b.ename
14                      ||' - '||c.ename,' - ')
15   from emp a
16        join
17        emp b on (a.empno=b.mgr)
18        left join
19        emp c on (b.empno=c.mgr)
20  where a.ename = 'KING'
21 union
22 select rtrim(a.ename||' - '||b.ename||' - '||
23              c.ename||' - '||d.ename,' - ')
24   from emp a
25        join
26        emp b on (a.empno=b.mgr)
27        join
28        emp c on (b.empno=c.mgr)
29        left join
30        emp d on (c.empno=d.mgr)
31  where a.ename = 'KING'
32        ) x
33  where tree is not null
34  order by 1

MySQL
使用 3 个 UNION 和多个自连接。

 1  select emp_tree
 2    from (
 3  select ename as emp_tree
 4    from emp
 5   where mgr is null
 6  union
 7 select concat(a.ename,' - ',b.ename)
 8   from emp a
 9        join
10        emp b on (a.empno=b.mgr)
11  where a.mgr is null
12 union
13 select concat(a.ename,' - ',
14               b.ename,' - ',c.ename)
15   from emp a
16        join
17        emp b on (a.empno=b.mgr)
18        left join
19        emp c on (b.empno=c.mgr)
20  where a.ename = 'KING'
21 union
22 select concat(a.ename,' - ',b.ename,' - ',
23               c.ename,' - ',d.ename)
24   from emp a
25        join
26        emp b on (a.empno=b.mgr)
27        join
28        emp c on (b.empno=c.mgr)
29        left join
30        emp d on (c.empno=d.mgr)
31  where a.ename = 'KING'
32        ) x
33  where tree is not null
34  order by 1

SQL 创建层次视图 扩展知识

DB2 和 SQL Server
首先识别出根节点（员工 KING）所在的行，这就是递归视图 X 中 UNION ALL 的前半部分。然后找出 KING 的下属，以及下属的下属（如果存在的话），这一步需要连接递归视图 X 和 EMP 表。递归操作会逐层遍历全体员工。递归视图 X 返回的结果集如下所示，此时尚未针对输出结果做格式化。

with x (ename,empno)
    as (
select cast(ename as varchar(100)),empno
  from emp
 where mgr is null
 union all
select cast(e.ename as varchar(100)),e.empno
  from emp e, x
 where e.mgr = x.empno
)
select ename emp_tree
  from x
　
EMP_TREE
-----------------
KING
JONES
SCOTT
ADAMS
FORD
SMITH
BLAKE
ALLEN
WARD
MARTIN
TURNER
JAMES
CLARK
MILLER

层次关系中的全部 ENAME 都被找出来了（这一点很有用），但因为没有做格式化，我们看不出哪些是管理者。把每个员工和他的管理者连接起来，就能得到更具可读性的输出结果了。下面的表达式出现在递归视图 X 里 UNION ALL 后半部分的 SELECT 子句中，它能生成所需格式的输出结果。

cast(x.ename+','+e.ename as varchar(100))

WITH 子句对于这一类问题非常有用，因为层次关系发生变动（例如，叶子节点变成了分支节点）时不需要修改查询。
Oracle
CONNECT BY 子句返回层次结构里的行。START WITH 子句定义根节点所在的行。如果不调用 SYS_CONNECT_BY_PATH 函数，我们会看到所需的各行数据都会被返回（这一点很有用），但因为还没做格式化，因而无法展现行之间的关系。

select ename emp_tree
  from emp
 start with mgr is null
connect by prior empno = mgr
　
EMP_TREE
-----------------
KING
JONES
SCOTT
ADAMS
FORD
SMITH
BLAKE
ALLEN
WARD
MARTIN
TURNER
JAMES
CLARK
MILLER

通过使用伪列 LEVEL 和函数 LPAD，我们能看到更清晰的层次关系，最终我们也会更深入地理解为什么 SYS_CONNECT_BY_PATH 能够返回我们所需的结果集。

select lpad('.',2*level,'.')||ename emp_tree
   from emp
 start with mgr is null
connect by prior empno = mgr

EMP_TREE
------------------
..KING
....JONES
......SCOTT
........ADAMS
......FORD
........SMITH
....BLAKE
......ALLEN
......WARD
......MARTIN
......TURNER
......JAMES
....CLARK
......MILLER

以上输出结果里的缩进距离表明了谁是管理者，他们的下属都会被嵌套进更深层的缩进里去。例如，KING 没有上级管理者。JONES 的管理者是 KING。SCOTT 的管理者是 JONES。ADAMS 的管理者是 SCOTT。
如果仔细观察 SYS_CONNECT_BY_PATH 函数连接的行，我们就会明白是 SYS_CONNECT_BY_PATH 展现出了层次关系。有了 SYS_CONNECT_BY_PATH，每当到达一个新节点，我们同时也能看到它的所有父节点。

KING
KING - JONES
KING - JONES - SCOTT
KING - JONES - SCOTT – ADAMS

　对于 Oracle 8i 或更早版本，不妨使用 PostgreSQL 解决方案。除此之外，因为更早版本的 Oracle 其实已经支持 CONNECT BY 子句，只要使用 LEVEL 和 RPAD/LPAD 格式化输出结果即可。（当然，模仿 SYS_CONNECT_BY_PATH 的输出格式要麻烦一些。）

PostgreSQL 和 MySQL
PostgreSQL 和 MySQL 解决方案除了 SELECT 子句里的字符串连接操作不同之外，其余部分都是一样的。首先要决定一个分支里最多会有多少个节点。在编写查询语句之前，我们必须手动完成这个计算。仔细观察 EMP 表的数据，我们会发现员工 ADAM 和 SMITH 是层次最深的叶子节点。（不妨参考 Oracle 解决方案的“讨论”部分，那里面已经用很美观的格式打印出了 EMP 表的层次关系。）我们来看一下员工 ADAMS，ADAMS 的管理者是 SCOTT，SCOTT 的管理者是 JONES，而 JONES 的管理者则是 KING，因此一共 4 层。为了展示 4 层的层次关系，我们必须一口气做 3 次 EMP 表的自连接查询，并且必须写一个包含有 4 个部分的 UNION 查询。下面展示了该自连接查询（即本解决方案中最下面那个 UNION 后面的查询）的结果。（这里使用的是 PostgreSQL 的语法，MySQL 用户需要把字符串连接操作符||改为 CONCAT 函数调用。）

select rtrim(a.ename||' - '||b.ename||' - '||
             c.ename||' - '||d.ename,' - ') as max_depth_4
  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'
　
MAX_DEPTH_4
-----------------------------
KING - JONES - FORD - SMITH
KING - JONES - SCOTT - ADAMS
KING - BLAKE - TURNER
KING - BLAKE - ALLEN
KING - BLAKE - WARD
KING - CLARK - MILLER
KING - BLAKE - MARTIN
KING - BLAKE - JAMES

A.ENAME 过滤条件是必须的，因为要确保根节点所在的行是 KING，而不是其他人。如果仔细观察以上结果集，并与最终结果集相比较的话，我们会发现某些 3 层关系的数据没有被打印出来：KING-JONES-FORD 和 KING-JONES-SCOTT。为了使最终结果集包含这些数据，我们需要再写一个类似于上述查询的语句，但要减少一个自连接（做 2 次自连接而不是 3 次）。该查询的结果集如下所示。

select rtrim(a.ename||' - '||b.ename
                    ||' - '||c.ename,' - ') as max_depth_3
  from emp a
       join
       emp b on (a.empno=b.mgr)
       left join
       emp c on (b.empno=c.mgr)
 where a.ename = 'KING'
　
MAX_DEPTH_3
---------------------------
KING - BLAKE - ALLEN
KING - BLAKE - WARD
KING - BLAKE - MARTIN
KING - JONES - SCOTT
KING - BLAKE - TURNER
KING - BLAKE - JAMES
KING - JONES - FORD
KING - CLARK - MILLER

像前一个查询一样，上述 A.ENAME 过滤条件也是必须的，因为要确保根节点是 KING。你可能会注意到这两个查询结果中会有一些重叠的行。为了剔除这些多余的行，只要把两个查询 UNION 起来即可。

select rtrim(a.ename||' - '||b.ename
                    ||' - '||c.ename,' - ') as partial_tree
  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'
　
PARTIAL_TREE
----------------------------
KING - BLAKE - ALLEN
KING - BLAKE - JAMES
KING - BLAKE - MARTIN
KING - BLAKE - TURNER
KING - BLAKE - WARD
KING - CLARK - MILLER
KING - JONES - FORD
KING - JONES - FORD - SMITH
KING - JONES - SCOTT
KING - JONES - SCOTT - ADAMS

现在，整个树形结构已经基本成型。下一步要找出以 KING 为根节点的、两层关系的行（即 KING 的直接下属）。返回这些行的查询如下所示。

select a.ename||' - '||b.ename as max_depth_2
  from emp a
       join
       emp b on (a.empno=b.mgr)
 where a.mgr is null
　
MAX_DEPTH_2
---------------
KING - JONES
KING - BLAKE
KING - CLARK

下一步需要把 PARTIAL_TREE 和上面的查询 UNION 起来。

select a.ename||' - '||b.ename as partial_tree
  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'
　
PARTIAL_TREE
----------------------------------
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

最后，把 KING 和 PARTIAL_TREE 也 UNION 起来以得到最终结果集。

Python教程

Python 教程

Python 教程
Tkinter 教程

Tkinter 教程
Pandas 教程

Pandas 教程
NumPy 教程

NumPy 教程
Flask 教程

Flask 教程
Django 教程

Django 教程
PySpark 教程

PySpark 教程
wxPython 教程

wxPython 教程
SymPy 教程

SymPy 教程
Seaborn 教程

Seaborn 教程
SciPy 教程

SciPy 教程
RxPY 教程

RxPY 教程
Pycharm 教程

Pycharm 教程
Pygame 教程

Pygame 教程
PyGTK 教程

PyGTK 教程
PyQt 教程

PyQt 教程
PyQt5 教程

PyQt5 教程
PyTorch 教程

PyTorch 教程
Matplotlib 教程

Matplotlib 教程
Web2py 教程

Web2py 教程
BeautifulSoup 教程

BeautifulSoup 教程

Java教程

Java 教程

Java 教程

Web教程

HTML 教程

HTML 教程
CSS 教程

CSS 教程
CSS3 教程

CSS3 教程
jQuery 教程

jQuery 教程
Ajax 教程

Ajax 教程
AngularJS 教程

AngularJS 教程
TypeScript 教程

TypeScript 教程
WordPress 教程

WordPress 教程
Laravel 教程

Laravel 教程
Next.js 教程

Next.js 教程
PhantomJS 教程

PhantomJS 教程
Three.js 教程

Three.js 教程
Underscore.JS 教程

Underscore.JS 教程
WebGL 教程

WebGL 教程
WebRTC 教程

WebRTC 教程
VueJS 教程

VueJS 教程

数据库教程

SQL 教程

SQL 教程
MySQL 教程

MySQL 教程
MongoDB 教程

MongoDB 教程
PostgreSQL 教程

PostgreSQL 教程
SQLite 教程

SQLite 教程
Redis 教程

Redis 教程
MariaDB 教程

MariaDB 教程

图形图像教程

Vulkan 教程

Vulkan 教程
OpenCV 教程

OpenCV 教程

大数据教程

R语言 教程

R语言 教程

开发工具教程

Git 教程

Git 教程
VSCode 教程

VSCode 教程
Docker 教程

Docker 教程
Gerrit 教程

Gerrit 教程
Excel 教程

Excel 教程

计算机教程

Go语言 教程

Go语言 教程
C++ 教程

C++ 教程

SQL 实例