递归查询

oracle
-------------------------------------------------------------------------------------------------
Start with...Connect By子句递归查询一般用于一个表维护树形结构的应用。
创建示例表:
CREATE TABLE TBL_TEST
(
  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'''');
 
从Root往树末梢递归
select * from TBL_TEST
 start with id=1
 connect by prior id = pid
 
从末梢往树ROOT递归
select * from TBL_TEST
 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
            

posted @   阿新  阅读(434)  评论(0编辑  收藏  举报
编辑推荐:
· 开发者必知的日志记录最佳实践
· 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
点击右上角即可分享
微信分享提示