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

SQL 层级查询(一)

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

我们经常用到的案例表 emp 就具有层次结构的数据,字段 empno 是员工编号,字段 mgr 则是员工的上级的编号。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

其中,mgr 为 NULL 表明该员工没有上级领导。

我们要把每个员工的所有上级领导都找出来,实现的效果如下:

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

对于编号为 7369 的 SMITH,他的直属领导的编号是 7902,姓名叫做 FORD;FORD 的直属领导叫做 JONES,编号为 7566;编号为 7566 的直属领导是编号为 7839 的 KING,而 KING 没有直属领导。因此,SMITH 的上级领导的关系链构成:->FORD->JONES->KING 。

对于找到每位员工的直属领导,使用一个自关联就能做到。

SELECT 
  a.empno,
  a.ename,
  b.ename AS leader
FROM
  emp a 
  LEFT JOIN emp b 
    ON b.empno = a.mgr 

由于 emp 表的层次关系的最大深度只有 4,因此,在原来查询父子关系的基础上再增加两个自关联就能把表中的所有关系都连接起来。

SELECT 
  a.empno,
  a.ename,
  CONCAT(
    IFNULL(CONCAT('->', b.ename), ''),
    IFNULL(CONCAT('->', c.ename), ''),
    IFNULL(CONCAT('->', d.ename), '')
  ) AS path 
FROM
  emp a 
  LEFT JOIN emp b 
    ON b.empno = a.mgr 
  LEFT JOIN emp c 
    ON c.empno = b.mgr 
  LEFT JOIN emp d 
    ON d.empno = c.mgr 

使用 IFNULL() 是为了避免当上级不存在时,ename 为 NULL 和所有字符串拼接的结果都为 NULL 。

如果层级太深,或者层次深度不确定,可以使用递归的方式解决。

WITH RECURSIVE leader_path(empno, ename, mgr, path) AS 
(SELECT 
  empno,
  ename,
  mgr,
  CAST('' AS CHAR(100)) AS path 
FROM
  emp 
UNION ALL 
SELECT 
  a.empno,
  a.ename,
  b.mgr,
  CONCAT(
    a.path,
    IFNULL(CONCAT('->', b.ename), '')
  ) 
FROM
  leader_path a 
  LEFT JOIN emp b 
    ON a.mgr = b.empno 
WHERE b.empno IS NOT NULL) 
SELECT 
  empno,
  ename,
  path 
FROM
  leader_path 
WHERE mgr IS NULL 
ORDER BY 1 

哈,使用递归的方案感觉要复杂了许多。使用递归需要注意几个地方:

  1. 在递归中一定要加入终止条件,本 SQL 的终止条件是 WHERE b.empno IS NOT NULL
  2. 遇到字符串拼接需要提前设置该字段的长度,对应到 SQL 中的操作是 CAST('' AS CHAR(100))
  3. 递归会生成中间结果,我们要把中间结果过滤掉,WHERE mgr IS NULL 就是只获取最终的结果。

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

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

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

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

我来说两句

0 条评论
登录 后参与评论

相关文章

  • SQL 层级查询(二)

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

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

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

    Lenis
  • SQL Server T-SQL高级查询

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

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

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

    用户1217611
  • SQL高级查询方法

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

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

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

    葡萄城控件
  • Oracle 高级查询-【联合语句】【联合查询】【层次查询】

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

    颍川
  • SQL查询的高级应用

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

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

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

    小手冰凉
  • 【T-SQL进阶】02.理解SQL查询的底层原理

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

    悟空聊架构
  • 秒级SQL查询性能提升方案

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

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

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

    Leshami
  • 你必须掌握的一些常见的SQL语句,包含单表查询、高级查询(连接查询、复合条件查询、嵌套查询)

    分享一些常见的SQL语句,包含单表查询、高级查询(连接查询、复合条件查询、嵌套查询等)。 --建立学生信息表Students create table Stud...

    用户1161731
  • Elasticsearch-sql 用SQL查询Elasticsearch

    Elasticsearch的查询语言(DSL)真是不好写,偏偏查询的功能千奇百怪,filter/query/match/agg/geo各种各样,不管你是通过封装...

    用户1225216
  • SQL数据查询之——嵌套查询

    Zoctopus
  • SQL数据查询之——单表查询

    Zoctopus
  • SQL连接查询和合并查询

    连接查询分为内连接,交叉连接,外连接,外连接又可以分为左外连接,右外连接和全外连接,现在我们就来区分一下他们: 内连接:采用inner join关键字 ? 举个...

    用户1171305
  • SQL基础查询

    对含有NULL数据的列使用DISTINCT关键字 NULL也被视为一类数据,如果存在多行NULL时,将被合并成一行。

    数据医生

扫码关注云+社区

领取腾讯云代金券