背景
在企业管理系统中，通常会有复杂的层级关系，比如员工与经理之间的关系，部门与子部门之间的关系等。在 SQL 查询中，如何有效地表示和操作这些层级关系是一个挑战。Oracle 数据库提供的 CONNECT BY
命令，让我们能够轻松地处理这些层级结构。
本文将详细介绍 Oracle 中 CONNECT BY
命令的使用，通过具体的示例展示其强大的层级查询功能。
什么是 CONNECT BY
？
CONNECT BY
是 Oracle 提供的一种层级查询机制，允许我们通过自引用的方式递归地查找层级关系。它可以帮助我们从一个层次出发，沿着某个关系路径，递归地查找其所有子节点或父节点。
主要使用方法包括：
查找某个节点的所有上级（从子节点到父节点）。
查找某个节点的所有下级（从父节点到子节点）。
示例数据
我们使用一个常见的员工表 emp
来展示 CONNECT BY
的用法：
select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
-- 查找员工编号为7876的上级:
select level,ENAME,EMPNO,MGR from emp e connect by prior e.mgr = e.empno start with e.empno = 7876 order by level;
LEVEL ENAME EMPNO MGR
---------- -------------------- ---------- ----------
1 ADAMS 7876 7788
2 SCOTT 7788 7566
3 JONES 7566 7839
4 KING 7839
-- 查找员工编号为7839的所有下级
select level, ENAME,EMPNO,MGR from emp e connect by prior e.empno = e.mgr start with e.empno = 7839 order by level;
LEVEL ENAME EMPNO MGR
---------- -------------------- ---------- ----------
1 KING 7839
2 JONES 7566 7839
2 BLAKE 7698 7839
2 CLARK 7782 7839
3 FORD 7902 7566
3 WARD 7521 7698
3 JAMES 7900 7698
3 MILLER 7934 7782
3 ALLEN 7499 7698
3 SCOTT 7788 7566
3 MARTIN 7654 7698
3 TURNER 7844 7698
4 ADAMS 7876 7788
4 SMITH 7369 7902
使用 lpad
函数可以格式化层级结果，使得结果更加直观：
select lpad(' ',(level-1)*2)||ename ename,empno,mgr from emp start with mgr is null connect by prior empno = mgr;
ENAME EMPNO MGR
-------------------- ---------- ----------
KING 7839
JONES 7566 7839
SCOTT 7788 7566
ADAMS 7876 7788
FORD 7902 7566
SMITH 7369 7902
BLAKE 7698 7839
ALLEN 7499 7698
WARD 7521 7698
MARTIN 7654 7698
TURNER 7844 7698
JAMES 7900 7698
CLARK 7782 7839
MILLER 7934 7782
总结
CONNECT BY
是 Oracle 强大的层级查询工具，可以轻松实现从顶层节点到子节点或从子节点到顶层节点的层级遍历。无论是在员工管理系统中查找上下级关系，还是在其他需要处理树形结构的场景中，CONNECT BY
都提供了便捷的解决方案。
通过本文的讲解和示例，希望你能够掌握 CONNECT BY
的使用方法，灵活应用于实际项目中，提升数据库查询和管理效率。
「欢迎关注我们的公众号，获取更多技术分享与经验交流。」
文章转载自数据库驾驶舱，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
