分层查询1、呈现父子关系2、呈现子–父–祖父关系3、创建基于表的分层视图4、找出给定父行的所有子行5、确定叶子节点、分支节点和根节点数据中可能存在层次关系本章介绍表达这种关系的实例。对于层次数据相比于对其进行存储对其进行检索并以层次方式呈现出来通常更难。几年前MySQL 引入了递归式 CTE现在大多数RDBMS 支持这种功能。因此使用递归式 CTE 已成为编写分层查询的标准方法。先来看看 EMP 表中 EMPNO 和 MGR 之间的层次关系。selectempno,mgrfromemporderby2;empno|mgr-------------7902|75667788|75667521|76987844|76987654|76987900|76987499|76987934|77827876|77887782|78397698|78397566|78397369|79027839|(14rows)如果仔细观察你将发现每个 MGR 值都是一个 EMPNO这意味着 EMP 表中的每位管理者也同样是员工且未被存储在其他地方。MGR 和 EMPNO 之间为父子关系因为EMPNO 对应的 MGR 值是它的直接父节点。对于特定的员工其管理者之上可能还有管理者而这些管理者之上也有管理者以此类推形成 n 层层次结构。对于没有管理者的员工其 MGR 值为 NULL。1、呈现父子关系问题你想在返回子记录中数据的同时返回父记录中的信息。例如你想显示每位员工的名字以及其管理者的名字。换言之你想返回如下结果集。EMPS_AND_MGRS------------------------------FORD worksforJONES SCOTT worksforJONES JAMES worksforBLAKE TURNER worksforBLAKE MARTIN worksforBLAKE WARD worksforBLAKE ALLEN worksforBLAKE MILLER worksforCLARK ADAMS worksforSCOTT CLARK worksforKING BLAKE worksforKING JONES worksforKING SMITH worksforFORD解决方案基于 MGR 和 EMPNO 相等自连接 EMP 表以找出每位员工的管理者的名字。然后使用 RDBMS 提供的字符串拼接函数生成所需的字符串。DB2、Oracle 和 PostgreSQL自连接 EMP 表然后使用表示拼接运算符的双竖线||。selecta.ename|| works for ||b.enameasemps_and_mgrsfromemp a,emp bwherea.mgrb.empno;emps_and_mgrs------------------------SMITH worksforFORD ALLEN worksforBLAKE WARD worksforBLAKE JONES worksforKING MARTIN worksforBLAKE BLAKE worksforKING CLARK worksforKING SCOTT worksforJONES TURNER worksforBLAKE ADAMS worksforSCOTT JAMES worksforBLAKE FORD worksforJONES MILLER worksforCLARK(13rows)MySQL自连接 EMP 表然后使用拼接函数 CONCAT。selectconcat(a.ename, works for ,b.ename)asemps_and_mgrsfromemp a,emp bwherea.mgrb.empno;SQL Server自连接 EMP 表然后使用表示拼接运算符的加号。selecta.ename works for b.enameasemps_and_mgrsfromemp a,emp bwherea.mgrb.empno;2、呈现子–父–祖父关系问题员工 CLARK 是 KING 的下属要表示这种关系可以使用上一节中的解决方案。如果员工 CLARK 还是另一位员工的管理者那么该如何表示这种关系呢请看下面的查询。selectename,empno,mgrfromempwhereenamein(KING,CLARK,MILLER);ENAME EMPNO MGR--------- -------- -------CLARK77827839KING7839MILLER79347782如你所见员工 MILLER 是 CLARK 的下属而 CLARK是 KING 的下属。你要呈现从 MILLER 到 KING 的完整层次结构。换言之你想返回如下结果集。LEAF___BRANCH___ROOT---------------------MILLER--CLARK--KING然而上一节使用的单次自连接方法无法呈现上述完整关系。虽然可以编写执行两次自连接的查询但使用遍历层次结构的通用方法更佳。解决方案本实例不同于上一个实例因为它要呈现的关系包含 3层。Oracle 提供了遍历树型数据的功能如果你使用的 RDBMS 没有提供这种功能则可以使用 CTE 来解决这个问题。DB2 和 SQL Server使用递归式 WITH 找出 MILLER 的管理者 CLARK再找出 CLARK 的管理者 KING。下面的解决方案使用的是SQL Server 字符串拼接运算符 。withx(tree,mgr,depth)as(selectcast(enameasvarchar(100)),mgr,0fromempwhereenameMILLERunionallselectcast(x.tree--e.enameasvarchar(100)),e.mgr,x.depth1fromemp e,xwherex.mgre.empno)selecttree leaf___branch___rootfromxwheredepth2;只要修改拼接运算符就可以将该解决方案用于其他数据库。换言之用于 DB2 时可以将拼接运算符改为||。MySQL 和 PostgreSQLMySQL 和 PostgreSQL 解决方案与上述解决方案类似只是需要添加关键字 RECURSIVE。WITHRECURSIVE x(tree,mgr,depth)AS(SELECTCAST(enameASCHAR(255)),mgr,0FROMempWHEREenameMILLERUNIONALLSELECTCONCAT(x.tree,--,e.ename),e.mgr,x.depth1FROMemp eJOINxONx.mgre.empno)SELECTtreeASleaf___branch___rootFROMxWHEREdepth2;Oracle使用函数 SYS_CONNECT_BY_PATH 返回 MILLER、MILLER 的管理者 CLARK 以及 CLARK 的管理者KING并使用 CONNECT BY 子句遍历树。selectltrim(sys_connect_by_path(ename,--),--)leaf___branch___rootfromempwherelevel3startwithenameMILLERconnectbyprior mgrempno;3、创建基于表的分层视图问题你想返回一个结果集将整张表的层次结构呈现出来。在EMP 表中员工 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解决方案DB2、PostgreSQL 和 SQL Server使用递归式 WITH 子句生成一个层次结构其中包含KING 及其管理的所有员工。下面展示的是 DB2 解决方案使用的是 DB2 拼接运算符 ||。要将该解决方案用于 SQL Server 和 MySQL只需在其中分别使用拼接运算符 和拼接函数 CONCAT。withRECURSIVE x(ename,empno)as(selectcast(enameasvarchar(100)),empnofromempwheremgrisnullunionallselectcast(x.ename|| - ||e.enameasvarchar(100)),e.empnofromemp e,xwheree.mgrx.empno)selectenameasemp_treefromxorderby1;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(14rows)MySQL在 MySQL 中还需添加关键字 RECURSIVE。WITHRECURSIVE x(ename,empno)AS(SELECTCAST(enameASCHAR(100)),empnoFROMempWHEREmgrISNULLUNIONALLSELECTCAST(CONCAT(x.ename, - ,e.ename)ASCHAR(255)),e.empnoFROMemp eJOINxONe.mgrx.empno)SELECTenameASemp_treeFROMxORDERBY1;Oracle使用函数 CONNECT BY 定义层次结构并使用函数SYS_CONNECT_BY_PATH 设置输出的格式。selectltrim(sys_connect_by_path(ename, - ), - )emp_treefromempstartwithmgrisnullconnectbyprior empnomgrorderby1;相比于上一节的解决方案该解决方案的不同之处在于没有使用基于伪列 LEVEL 的筛选器。删除这个筛选器后将显示所有可能的树符合条件 PRIOR EMPNOMGR 的树。4、找出给定父行的所有子行问题你想找出 JONES 的所有下属包括直接下属和间接下属JONES 的下属的下属。下面列出了 JONES 及其所有下属。ENAME----------JONES SCOTT ADAMS FORD SMITH解决方案能够定位到树的顶部或底部很有用。在本解决方案中不需要特殊的格式设置。这里的目标很简单就是返回JONES 下属的所有员工包括 JONES 自己。这种查询充分展示了递归式 SQL 扩展比如 Oracle 的 CONNECTBY 以及 SQL Server 和 DB2 的 WITH 子句的威力。DB2、PostgreSQL 和 SQL Server使用递归式 WITH 子句找出是 JONES 下属的所有员工。从 JONES 开始在 UNION ALL 上半部分的查询中指定WHERE ENAME JONES。withx(ename,empno)as(selectename,empnofromempwhereenameJONESunionallselecte.ename,e.empnofromemp e,xwherex.empnoe.mgr)selectenamefromx;Oracle使用 CONNECT BY 子句并指定 START WITH ENAME JONES以找出 JONES 下属的所有员工。selectenamefromempstartwithenameJONESconnectbyprior empnomgr;5、确定叶子节点、分支节点和根节点问题你想判断给定的行是哪种类型的节点叶子节点、分支节点还是根节点。在本实例中叶子节点指的是不是管理者的员工分支节点指的是自己是管理者且还有上级管理者的员工而根节点指的是没有上级管理者的员工。对于层次结构中的每一行你都要返回 1TRUE或 0FALSE以指出其状态。你希望返回的结果集如下所示。ENAME IS_LEAF IS_BRANCH IS_ROOT---------- ---------- ---------- ----------KING001JONES010SCOTT010FORD010CLARK010BLAKE010ADAMS100MILLER100JAMES100TURNER100ALLEN100WARD100MARTIN100SMITH100解决方案EMP 表建立的是树型层次结构而不是递归层次结构因为根节点的 MGR 为 NULL认识到这一点很重要。如果EMP 建立的是递归层次结构那么根节点将指向自己也就是说员工 KING 的 MGR 值将为他的 EMPNO。我们发现指向自己是不合常理的因此将根节点的 MGR 设置为了 NULL。使用 Oracle 的 CONNECT BY 以及 DB2和 SQL Server 的 WITH 子句时你会发现树型层次结构比递归层次结构更容易处理效率也更高。使用CONNECT BY 或 WITH 处理递归层次结构时务必小心因为最终编写的 SQL 代码可能包含循环。如果处理递归层次结构时出现问题那么请务必检查这种循环。DB2、PostgreSQL、MySQL 和 SQL Server使用 3 个标量子查询在每个节点类型列中返回正确的“布尔”值1 或 0。selecte.ename,(selectsign(count(*))fromemp dwhere0(selectcount(*)fromemp fwheref.mgre.empno))asis_leaf,(selectsign(count(*))fromemp dwhered.mgre.empnoande.mgrisnotnull)asis_branch,(selectsign(count(*))fromemp dwhered.empnoe.empnoandd.mgrisnull)asis_rootfromemp eorderby4desc,3desc;Oracle上述子查询解决方案也适用于 Oracle。如果你使用的是Oracle Database 10g 以前的版本那么也应该使用这种解决方案。下面的解决方案使用了 Oracle 提供的内置函数 CONNECT_BY_ROOT 和 CONNECT_BY_ISLEAF这些内置函数是 Oracle Database 10g 引入的来找出根行和叶子行。selectename,connect_by_isleaf is_leaf,(selectcount(*)fromemp ewheree.mgremp.empnoandemp.mgrisnotnullandrownum1)is_branch,decode(ename,connect_by_root(ename),1,0)is_rootfromempstartwithmgrisnullconnectbyprior empnomgrorderby4desc,3desc;
企业数字化 ERP 产品动态
相关推荐
龙虾智能体进阶实战：自定义Skill插件开发与多模型适配方案 龙虾智能体进阶实战：自定义 Skill 插件开发与多模型适配方案
引言
如果说基础部署是让龙虾"活起来"，那么自定义 Skill 插件开发就是赋予它独特的技能。OpenClaw 的真正威力在于其可扩展性——你可以为任何重复性工作流编写 Skill，让 Agent 成为真正懂你业务的数… · 2026/9/25 1:19:46
WarcraftHelper完整指南：让经典魔兽争霸3焕发新生的终极免费工具 WarcraftHelper完整指南：让经典魔兽争霸3焕发新生的终极免费工具 【免费下载链接】WarcraftHelper Warcraft III Helper , support 1.20e, 1.24e, 1.26a, 1.27a, 1.27b 项目地址: https://gitcode.com/gh_mirrors/wa/WarcraftHelper
还在为《魔兽争霸3》这款… · 2026/9/25 17:02:16
从FWHM到σ：高斯波形解析中的关键几何关系与物理意义 1. 高斯波形解析中的核心参数：FWHM与σ
当你第一次看到激光雷达波形图时，可能会被那些起伏的曲线搞得一头雾水。别担心，我们今天要聊的FWHM和σ，就是解读这些波形的"密码本"。FWHM全称Full-width at half maximum&#… · 2026/9/24 8:15:55
美赛各题型代码包实战指南：从熵权TOPSIS到蒙特卡洛的快速上手 简介：这份资源面向参加数学建模竞赛（尤其是美赛）的学生与研究者，系统整理了各常见题型的参考代码，覆盖从线性回归等基础方法到遗传算法改进神经网络等进阶模型，适合需要快速搭建求解框架、对照复现算法的中… · 2026/9/26 6:59:12
PHP fork炸弹原理与防御：从进程爆炸到系统救援 有一次周五晚上十一点，监控告警短信把我手机震成了震动按摩仪：那台跑着PHP应用的服务器load average正以肉眼可见的速度朝300冲去。SSH连上去卡了快半分钟才弹出提示符，敲一条ls都要等好几秒。翻遍应用日志之后，最后在一个临时目录… · 2026/9/26 6:59:12
Claude代码生成模板：基于CLI与npm的工程化接入方案 1. 项目概述：这不是一个“插件”，而是一套可复用的代码生成工作流 “claude-code-templates”这个标题乍看像某个第三方VS Code扩展，但实际拆解后你会发现，它根本不是传统意义上的图形界面工具——它是一套面向CLI（命… · 2026/9/26 6:59:12
Jev 搭配 Exa 联网搜索：本地大模型实时问答实战指南 1. 从标题说起：Jev 与 Exa 的组合到底解决了什么问题第一次看到“Jev 搭配 Exa 联网搜索效果惊人”这个说法，我的反应是：又是一个把两个工具拼在一起就喊“效果惊人”的标题。但真正动手把这两个东西接起来跑通之后，我承认这个评价… · 2026/9/26 6:59:12
小红书内容下载终极指南：截图/网页解析/本地爬虫三法 1. 为什么小红书内容“看起来能点保存，实际却总差一步”？小红书的图文和视频内容，视觉上非常精致——高清图、流畅动图、带字幕的竖屏短视频，随手一刷就是信息密度极高的种草现场。但你有没有试过：看到一张绝美穿搭图想… · 2026/9/26 6:59:06
数据库课后习题答案别硬背：当测试用例集刷，效率翻倍 简介：万常选版《数据库原理与设计》课后习题答案资源，覆盖第2至6章及第9章，适合正在学习关系模型、数据库建模、关系数据理论与模式求精的本科生、自学者作为复习与自测材料。压缩包共7个文件，含3个doc参考答案、2个sql示例脚本、… · 2026/9/26 0:00:21
OpenClaw 替代品？Hermes Agent 踩坑实录：macOS 飞书接入 TaoToken 配置 /* MD / 富文本中的 .toc（含博客园搬家等嵌套结构）；.toc-box 在侧栏，不受影响 */#content_views .toc,/* 编辑器常在目录前后插入空 p（:empty 仍占 20px），一并去掉避免顶空隙 */#content_views.markdown_views > p:empty:has(+ .toc),#content_views.markdown_views … · 2026/9/26 0:00:40
向下兼容与向上兼容：接口设计中的兼容性策略与工程实践 一次版本升级事故，是很多团队绕不过去的坎。线上环境里，服务端明明已经上线了新版接口，老的移动端还在照着旧文档传参数。请求一到网关，校验直接拒绝，用户操作失败，客服群炸了锅，开发群里开始互… · 2026/9/26 0:00:46
