递归查询
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 | |