递归查询

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 @ 2007-05-30 15:59  阿新  阅读(427)  评论(0)  编辑  收藏  举报