专栏首页SQL实现SQL 层级查询(二)

SQL 层级查询(二)

上一篇文章里,我们介绍了在 MySQL 中实现层次查询的两种方式。前文举的示例是获取从叶子点到根节点的路径,今天我们要实现的是从根节点找到所有叶子节点。

依旧以 emp 表为例,遍历所有员工数据,计算每个员工所在的层级(假设根节点所在层级为 1,mgr 为 NULL 的员工所在的节点为根节点 )。

emp 表的部分数据如下:

 empno  ename      mgr  
------  ------  --------
  7369  SMITH       7902
  7499  ALLEN       7698
  7521  WARD        7698
  7566  JONES       7839
  7654  MARTIN      7698
  7698  BLAKE       7839
  7782  CLARK       7839
  7788  SCOTT       7566
  7839  KING      (NULL)
  7844  TURNER      7698
  7876  ADAMS       7788
  7900  JAMES       7698
  7902  FORD        7566
  7934  MILLER      7782

比如,KING 的节点为根节点,层级为 1,它有三个子节点,分别对应的编号是:7566、7698、7782,它们的层级为 2;其中,编号为 7566 的 JONES 有两个子节点:7788 和 7902,它们对应的层级为 3。

即使我们知道 emp 表中的员工的关系最深只有 4 级,使用多个自关联依然没法直接计算出各个员工的层级。因此,我们暂且用递归的方式实现。

锁定根节点:

SELECT 
  empno,
  ename,
  mgr,
  1 AS lv 
FROM
  emp 
WHERE mgr IS NULL 

找到根节点下的子节点:

SELECT 
  b.empno,
  b.ename,
  b.mgr,
  lv + 1 
FROM
  (SELECT 
    empno,
    ename,
    mgr,
    1 AS lv 
  FROM
    emp 
  WHERE mgr IS NULL) a 
  INNER JOIN emp b 
    ON a.empno = b.mgr; 
    

 empno  ename      mgr  lv + 1  
------  ------  ------  --------
  7566  JONES     7839         2
  7698  BLAKE     7839         2
  7782  CLARK     7839         2

再把父子节点的关系套入递归表达式模板,最终的 SQL :

WITH RECURSIVE leader_path (empno, ename, mgr, lv) AS 
(SELECT 
  empno,
  ename,
  mgr,
  1 AS lv
FROM
  emp WHERE mgr IS NULL
UNION ALL 
SELECT 
  b.empno,
  b.ename,
  b.mgr,
  lv + 1
FROM
  leader_path a 
  INNER JOIN emp b 
    ON a.empno = b.mgr ) 
SELECT 
  empno,
  ename,
  lv 
FROM
  leader_path 
ORDER BY 1 

所有员工的层级 >>>

 empno  ename       lv  
------  ------  --------
  7369  SMITH          4
  7499  ALLEN          3
  7521  WARD           3
  7566  JONES          2
  7654  MARTIN         3
  7698  BLAKE          2
  7782  CLARK          2
  7788  SCOTT          3
  7839  KING           1
  7844  TURNER         3
  7876  ADAMS          4
  7900  JAMES          3
  7902  FORD           3
  7934  MILLER         3

本文分享自微信公众号 - SQL实现(gh_684ee9235a26),作者:zero

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-12-03

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • SQL 层级查询(一)

    相信大家在工作中都遇到过存在层次关系的数据表,典型的例子诸如菜单表(多级菜单)、用户表(拥有上下级关系)、商品类目表(多级类目)。

    白日梦想家
  • SQL 高级查询 ——(层次化查询,递归)

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

    Lenis
  • SQL Server T-SQL高级查询

    高级查询在数据库中用得是最频繁的,也是应用最广泛的。 Ø 基本常用查询 --select select * from student;   --all 查询所有...

    Java中文社群-磊哥
  • mysql 层级结构查询

    描述:最近遇到了一个问题,在mysql中如何完成节点下的所有节点或节点上的所有父节点的查询? 在Oracle中我们知道有一个Hierarchical Queri...

    用户1217611
  • SQL高级查询方法

    子查询是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句或其他子查询中的查询。任何允许使用表达式的地方都可以使用子查询。

    fireWang
  • Oracle 高级查询-【联合语句】【联合查询】【层次查询】

    版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。 ...

    颍川
  • SQL查询的高级应用

    简单的Transact-SQL查询只包括选择列表、FROM子句和WHERE子句。它们分别说明所查询列、查询的表或视图、以及搜索条件等。

    阳光岛主
  • 浅谈 T-SQL高级查询

    之前我们简单的了解了增、删、改、查这几类T-SQL语法来操纵数据表,但是为了更方便快捷地完成大量任务,SQL Server 提供了一些内部函数,可以和SQL S...

    小手冰凉
  • SQL基本查询语句(二)

    无论是基础查询还是条件查询,最终的结果都是显示了所有字段。即:包含了id, class_id, name, gender, score。如果我们只关心name字...

    zy010101
  • Mybatis高级查询(二):多表联合查询

    提供pojo类并提供get/set方法以及重写toString方法,这里以User类为例,除User类以外还应提供数据库表对应的类,如Orders,item等

    许喜朝
  • 分页查询,二级条件

    分页查询是MySQL特有的,一般其他数据库是没有的。分页查询可以从表里取一个范围的行,例如0到50行的的数据,30到100行的数据。

    端碗吹水
  • 【T-SQL进阶】02.理解SQL查询的底层原理

    悟空聊架构
  • 【T-SQL进阶】02.理解SQL查询的底层原理

    悟空聊架构
  • T-SQL基础(二)之关联查询

    表运算符的作用是把为其提供的表作为输入,经过逻辑查询处理,返回一个表结果。SQL Server支持四个表运算符:JOIN、APPLY、PIVOT、UNPIVOT...

    雪飞鸿
  • T-SQL基础(二)之关联查询

    表运算符的作用是把为其提供的表作为输入,经过逻辑查询处理,返回一个表结果。SQL Server支持四个表运算符:JOIN、APPLY、PIVOT、UNPIVOT...

    雪飞鸿
  • Sql学习笔记(二)—— 条件查询

    上篇简单介绍了一下sql的一些基础增删改查语句,而针对多种多样的查询语句则未详细说明,这一篇继续记录一下关于各种条件查询的知识。

    CherishTheYouth
  • 一句SQL完成动态分级查询

    在最近的活字格项目中使用ActiveReports报表设计器设计一个报表模板时,遇到一个多级分类的难题:需要将某个部门所有销售及下属部门的销售金额汇总,因为下属...

    葡萄城控件
  • 秒级SQL查询性能提升方案

    1、建立高效且合适的索引 因为索引既有数据特征、也有业务特征,数据量的变化会影响索引的选择,业务特点不一样,索引的优化思路也不一样。通常某个字段平时不用,但是...

    MickyInvQ
  • SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)

    --======================================================

    Leshami

扫码关注云+社区

领取腾讯云代金券