递归查询
oracle
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
Start with...Connect By子句递归查询一般用于一个表维护树形结构的应用。
创建示例表:
CREATE TABLE TBL_TEST
(
ID NUMBER,
NAME VARCHAR2(100 BYTE),
PID NUMBER DEFAULT 0
);
(
ID NUMBER,
NAME VARCHAR2(100 BYTE),
PID NUMBER DEFAULT 0
);
插入测试数据:
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES(''''1'''',''''10'''',''''0'''');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES(''''2'''',''''11'''',''''1'''');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES(''''3'''',''''20'''',''''0'''');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES(''''4'''',''''12'''',''''1'''');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES(''''5'''',''''121'''',''''2'''');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES(''''2'''',''''11'''',''''1'''');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES(''''3'''',''''20'''',''''0'''');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES(''''4'''',''''12'''',''''1'''');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES(''''5'''',''''121'''',''''2'''');
从Root往树末梢递归
select * from TBL_TEST
start with id=1
connect by prior id = pid
start with id=1
connect by prior id = pid
从末梢往树ROOT递归
select * from TBL_TEST
start with id=5
connect by prior pid = id
MSSQL
----------------------------------------------------------------------------------
start with id=5
connect by prior pid = id
MSSQL
----------------------------------------------------------------------------------
使用递归公用表表达式显示递归的多个级别。
以下示例显示经理以及向经理报告的雇员的层次列表。
USE AdventureWorks; GO WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS ( SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel FROM HumanResources.Employee WHERE ManagerID IS NULL UNION ALL SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1 FROM HumanResources.Employee e INNER JOIN DirectReports d ON e.ManagerID = d.EmployeeID ) SELECT ManagerID, EmployeeID, EmployeeLevel FROM DirectReports ; GO |
E. 使用递归公用表表达式显示递归的两个级别。
以下示例显示经理以及向经理报告的雇员。将返回的级别数目被限制为两个。
USE AdventureWorks; GO WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS ( SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel FROM HumanResources.Employee WHERE ManagerID IS NULL UNION ALL SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1 FROM HumanResources.Employee e INNER JOIN DirectReports d ON e.ManagerID = d.EmployeeID ) SELECT ManagerID, EmployeeID, EmployeeLevel FROM DirectReports WHERE EmployeeLevel <= 2 ; GO |
F. 使用递归公用表表达式显示层次列表
以下示例在示例 C 的基础上添加经理和雇员的名称,以及他们各自的头衔。通过缩进各个级别,突出显示经理和雇员的层次结构。
USE AdventureWorks; GO WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort) AS (SELECT CONVERT(varchar(255), c.FirstName + ' ' + c.LastName), e.Title, e.EmployeeID, 1, CONVERT(varchar(255), c.FirstName + ' ' + c.LastName) FROM HumanResources.Employee AS e JOIN Person.Contact AS c ON e.ContactID = c.ContactID WHERE e.ManagerID IS NULL UNION ALL SELECT CONVERT(varchar(255), REPLICATE ('| ' , EmployeeLevel) + c.FirstName + ' ' + c.LastName), e.Title, e.EmployeeID, EmployeeLevel + 1, CONVERT (varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' + LastName) FROM HumanResources.Employee as e JOIN Person.Contact AS c ON e.ContactID = c.ContactID JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID ) SELECT EmployeeID, Name, Title, EmployeeLevel FROM DirectReports ORDER BY Sort; GO |
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列:如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列(二):开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· Manus重磅发布:全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后,我竟然真的恢复了删除的微信聊天记录!
· 没有Manus邀请码?试试免邀请码的MGX或者开源的OpenManus吧
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· 【自荐】一款简洁、开源的在线白板工具 Drawnix