Oracle与SQL递归查询-优快云博客

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

复制代码

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

复制代码

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

复制代码

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