SQL 高级查询—层次化查询、递归

数据大师
数据大师
数据大师
294
文章
0
评论
2021-06-1616:33:49 评论 803 1809字
摘要

层次化结构可以理解为树状数据结构,由节点构成。比如常见的组织结构由一个总经理,多个副总经理,多个部门部长组成。再比如在生产制造中一件产品会有多个子零件组成。

一、层次化查询

层次化结构可以理解为树状数据结构,由节点构成。比如常见的组织结构由一个总经理,多个副总经理,多个部门部长组成。再比如在生产制造中一件产品会有多个子零件组成。举个简单的例子,如下图所示

SQL 高级查询—层次化查询、递归

汽车作为根节点,下面包含发动机和车身两个子节点,而子节点又是由其他叶节点构成。(叶节点表示没有子节点的节点)

假如我们要把这些产品信息存储到数据库中,会形成如下数据表。

SQL 高级查询—层次化查询、递归

我们用 parent_product_id 列表示当前产品的父产品是哪一个。

那么用 SQL 语句如何进行层次化查询呢?这里就要用到 CONNECT BY 和 START WITH 语法。我们先把 SQL 写出来,再来解释其中的含义。

SELECT  level,  id,  parent_product_id,  nameFROM  product  START WITH id  = 1  CONNECT BY prior id = parent_product_idORDER BY  level

查询结果如下:

SQL 高级查询—层次化查询、递归

解释一下:LEVEL 列表示当前产品属于第几层级。START WITH 表示从哪一个产品开始查询,CONNECT BY PRIOR 表示父节点与子节点的关系,每一个产品的 ID 指向一个父产品。

如果我们把 START WITH 的查询起点改为 id = 2,重新运行上面的 SQL 语句将会得到如下结果:

SQL 高级查询—层次化查询、递归

因为 id=2 的产品是车身,我们就只能查到车身下面的子产品。

当然,我们可以把查询结果美化一下,使其更有层次感,我们让根节点下面的 LEVEL 前面加几个空格即可。把上面的 SQL 稍微修改一下。为每个 LEVEL 前面增加 2*(LEVEL-1)个空格,这样第二层就会增加两个空格,第三层会增加四个空格。

SELECT  level,  id,  parent_product_id,  LPAD(" ", 2 * (level - 1)) || name AS nameFROM  product  START WITH id  = 1  CONNECT BY prior id = parent_product_id

查询结果已经有了层次感,如下图:

SQL 高级查询—层次化查询、递归

二、 递归查询

除了使用上面我们说的方法,还可以使用递归查询得到同样的结果。递归会用到 WITH 语句。普通的 WITH 语句可以看作一个子查询,我们在 WITH 外部可以直接使用这个子查询的内容。

当递归查询时,我们是在 WITH 语句内部来引用这个子查询。还是上面的例子,我们使用 WITH 语句来查询。

WITH  temp_product (product_level, id, parent_product_id,name) AS  (    SELECT      0 AS product_level,id,parent_product_id,name    FROM      product    WHERE      parent_product_id IS NULL    UNION ALL    SELECT      tp.product_level + 1,p.id,      p.parent_product_id,      p.name    FROM      product p    JOIN temp_product tp    ON      p.parent_product_id=tp.id  )SELECT  product_level,  id,  parent_product_id,  LPAD(" ", 2 * product_level)  || name AS NAMEFROM  temp_product;

第一条 SELECT 语句我们查询出来了根节点,并且设置为 level = 0,第二条SELECT 语句关联上 WITH 语句自身,并且 level 每层加 1 进行递归。

查询结果如下:

SQL 高级查询—层次化查询、递归

可以看到第一列是展示的产品层级,和我们上面查询出来的结果是一致的。

同时使用 WITH 递归时还可以使用深度优先搜索和广度优先搜索,什么意思呢?广度优先就是在返回子行之前首先返回兄弟行,如上图,首先把车身和发动机两个兄弟行返回,之后是他们下面的子行。相反,深度优先就是首先返回一个父节点的子行再返回另一个兄弟行。

我们只需要在 SELECT 语句上方加上下面语句即可实现深度优先搜索查询。

  search depth FIRST BY id  SET order_by_id

结果如下,看到首先返回每个父节点下的子行,再返回另一个父节点。

同理,广度优先使用的是下面的 SQL 语句

  search breadth FIRST BY id  SET order_by_id

 

End.

来源:智能制造社区

转载如果涉及作品问题请联们第一时间删除(微信lovedata0520

更多文章前往首页浏览http://www.itongji.cn/

  • 我的微信公众号
  • 微信扫一扫
  • weinxin
  • 我的微信公众号
  • 微信扫一扫
  • weinxin
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: