SQL查找字符级节点

组织结构表如下o_org：

id	name	pid
1	部门1	3
3	分公司A	8
5	部门2	3
8	集团	-1
9	部门3	10
10	分公司B	8

组织结构树如下：

搜索集团与其下的所有分公司和部门：

with org1as(

select *from o_org where id= 8 

union all 

select o_org.*from org1 inner join o_orgon  org1.id=o_org.pid

)

select *from org1;

搜索部门1与其所有父级节点：

with org1as(

select *from o_org where o_org.id= 1 

union all

select a.*from o_org a join org1b on a.id=b.pid 

)

select *from  org1;    

 

关于WITH AS的使用方法可以参见SQLSERVER的帮助文档，如下参见SQLSERVER2008文档说明：

指定临时命名的结果集，这些结果集称为公用表表达式 (CTE)。该表达式源自简单查询，并且在单条 SELECT、INSERT、UPDATE 或 DELETE 语句的执行范围内定义。该子句也可用在 CREATE VIEW 语句中，作为该语句的 SELECT 定义语句的一部分。公用表表达式可以包括对自身的引用。这种表达式称为递归公用表表达式。

语法

[ WITH <common_table_expression> [ ,...n ] ]   <common_table_expression>::=         expression_name [ ( column_name [ ,...n ] ) ]     AS         ( CTE_query_definition )

参数

expression_name

公用表表达式的有效标识符。 expression_name 必须与在同一 WITH <common_table_expression> 子句中定义的任何其他公用表表达式的名称不同，但 expression_name可以与基表或基视图的名称相同。在查询中对 expression_name 的任何引用都会使用公用表表达式，而不使用基对象。

column_name

在公用表表达式中指定列名。在一个 CTE 定义中不允许出现重复的名称。指定的列名数必须与 CTE_query_definition结果集中列数匹配。只有在查询定义中为所有结果列都提供了不同的名称时，列名称列表才是可选的。

CTE_query_definition

指定一个其结果集填充公用表表达式的 SELECT 语句。除了 CTE 不能定义另一个 CTE 以外，CTE_query_definition 的 SELECT 语句必须满足与创建视图时相同的要求。有关详细信息，请参阅“备注”部分和 CREATE VIEW (Transact-SQL)。

如果定义了多个 CTE_query_definition，则这些查询定义必须用下列一个集合运算符联接起来：UNION ALL、UNION、EXCEPT 或 INTERSECT。有关如何使用递归 CTE 查询定义的详细信息，请参阅下面的“备注”部分和使用公用表表达式的递归查询。

备注

创建和使用公用表表达式的准则

下面的准则适用于非递归公用表表达式。有关适用于递归公用表表达式的准则，请参阅后面的“定义和使用递归公用表表达式的准则”。

·        CTE 之后必须跟随引用部分或全部 CTE 列的单条 SELECT、INSERT、UPDATE 或 DELETE 语句。也可以在 CREATE VIEW 语句中将 CTE 指定为视图中 SELECT 定义语句的一部分。

·        可以在非递归 CTE中定义多个 CTE查询定义。定义必须与以下集合运算符之一结合使用：UNION ALL、UNION、INTERSECT 或 EXCEPT。

·        CTE 可以引用自身，也可以引用在同一 WITH 子句中预先定义的 CTE。不允许前向引用。

·        不允许在一个 CTE中指定多个 WITH 子句。例如，如果 CTE_query_definition包含一个子查询，则该子查询不能包括定义另一个 CTE 的嵌套的 WITH 子句。

·        不能在 CTE_query_definition中使用以下子句：

·        COMPUTE 或 COMPUTE BY

·        ORDER BY（除非指定了 TOP 子句）

·        INTO

·        带有查询提示的 OPTION 子句

·        FOR XML

·        FOR BROWSE

·        如果将 CTE用在属于批处理的一部分的语句中，那么在它之前的语句必须以分号结尾。

·        可以使用引用 CTE的查询来定义游标。

·        可以在 CTE中引用远程服务器中的表。

·        在执行 CTE时，任何引用 CTE的提示都可能与该 CTE访问其基础表时发现的其他提示相冲突，这种冲突与引用查询中的视图的提示所发生的冲突相同。发生这种情况时，查询将返回错误。有关详细信息，请参阅视图解析。

定义和使用递归公用表表达式的准则

下面的准则适用于定义递归公用表表达式：

·        递归 CTE定义至少必须包含两个 CTE查询定义，一个定位点成员和一个递归成员。可以定义多个定位点成员和递归成员；但必须将所有定位点成员查询定义置于第一个递归成员定义之前。所有 CTE 查询定义都是定位点成员，但它们引用 CTE 本身时除外。

·        定位点成员必须与以下集合运算符之一结合使用：UNION ALL、UNION、INTERSECT 或 EXCEPT。在最后一个定位点成员和第一个递归成员之间，以及组合多个递归成员时，只能使用 UNION ALL 集合运算符。

·        定位点成员和递归成员中的列数必须一致。

·        递归成员中列的数据类型必须与定位点成员中相应列的数据类型一致。

·        递归成员的 FROM 子句只能引用一次 CTEexpression_name。

·        在递归成员的 CTE_query_definition中不允许出现下列项：

·        SELECT DISTINCT

·        GROUP BY

·        HAVING

·        标量聚合

·        TOP

·        LEFT、RIGHT、OUTER JOIN（允许出现 INNER JOIN）

·        子查询

·        应用于对 CTE_query_definition中的 CTE的递归引用的提示。

下面的准则适用于使用递归公用表表达式：

·        无论参与的 SELECT 语句返回的列的为 Null 性如何，递归 CTE返回的全部列都可以为空。

·        如果递归 CTE组合不正确，可能会导致无限循环。例如，如果递归成员查询定义对父列和子列返回相同的值，则会造成无限循环。可以使用 MAXRECURSION 提示以及在 INSERT、UPDATE、DELETE 或 SELECT 语句的 OPTION 子句中的一个 0 到 32,767 之间的值，来限制特定语句所允许的递归级数，以防止出现无限循环。这样就能够在解决产生循环的代码问题之前控制语句的执行。服务器范围内的默认值是 100。如果指定 0，则没有限制。每一个语句只能指定一个 MAXRECURSION 值。有关详细信息，请参阅查询提示 (Transact-SQL)。

·        不能使用包含递归公用表表达式的视图来更新数据。

·        可以使用 CTE在查询上定义游标。CTE 是定义游标结果集的 select_statement参数。递归 CTE只允许使用快速只进游标和静态（快照）游标。如果在递归 CTE 中指定了其他游标类型，则该类型将转换为静态游标类型。

·        可以在 CTE中引用远程服务器中的表。如果在 CTE 的递归成员中引用了远程服务器，那么将为每个远程表创建一个假脱机，这样就可以在本地反复访问这些表。

·        SQL Server 2008 不允许在 CTE 的递归部分中使用分析和聚合函数。

示例

A. 创建一个简单公用表表达式

以下示例显示直接向 Adventure Works Cycles 的每个经理报告的雇员的数目。

USE AdventureWorks; GO WITH DirReps(ManagerID, DirectReports) AS (     SELECT ManagerID, COUNT(*)     FROM HumanResources.Employee AS e     WHERE ManagerID IS NOT NULL     GROUP BY ManagerID ) SELECT ManagerID, DirectReports FROM DirReps ORDER BY ManagerID; GO

B. 使用公用表表达式来限制次数和报告平均数

以下示例显示向经理报告的雇员的平均数。

复制代码

WITH DirReps (Manager, DirectReports) AS (     SELECT ManagerID, COUNT(*) AS DirectReports     FROM HumanResources.Employee     GROUP BY ManagerID ) SELECT AVG(DirectReports) AS [Average Number of Direct Reports] FROM DirReps WHERE DirectReports>= 2 ; GO

C. 多次引用同一个公用表表达式

以下示例显示 SalesOrderHeader 表中每个销售人员的销售订单的总数和最近的销售订单的日期。CTE 在运行的语句中被引用两次：一次返回为销售人员所选的列，另一次检索销售经理的类似详细信息。销售人员和销售经理的数据都返回在一行中。

复制代码

USE AdventureWorks; GO WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate) AS (     SELECT SalesPersonID, COUNT(*), MAX(OrderDate)     FROM Sales.SalesOrderHeader     GROUP BY SalesPersonID ) SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,     E.ManagerID, OM.NumberOfOrders, OM.MaxDate FROM HumanResources.Employee AS E     JOIN Sales_CTE AS OS     ON E.EmployeeID = OS.SalesPersonID     LEFT OUTER JOIN Sales_CTE AS OM     ON E.ManagerID = OM.SalesPersonID ORDER BY E.EmployeeID; 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

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

G. 使用MAXRECURSION 取消一条语句

可以使用 MAXRECURSION 来防止不合理的递归 CTE 进入无限循环。以下示例特意创建了一个无限循环，然后使用 MAXRECURSION 提示将递归级别限制为两级。

复制代码

USE AdventureWorks; GO --Creates an infinite loop WITH cte (EmployeeID, ManagerID, Title) as (     SELECT EmployeeID, ManagerID, Title     FROM HumanResources.Employee     WHERE ManagerID IS NOT NULL   UNION ALL     SELECT cte.EmployeeID, cte.ManagerID, cte.Title     FROM cte     JOIN  HumanResources.Employee AS e         ON cte.ManagerID = e.EmployeeID ) --Uses MAXRECURSION to limit the recursive levels to 2 SELECT EmployeeID, ManagerID, Title FROM cte OPTION (MAXRECURSION 2); GO

在更正代码错误之后，就不再需要 MAXRECURSION。以下示例显示了更正后的代码。

复制代码

USE AdventureWorks; GO WITH cte (EmployeeID, ManagerID, Title) AS (     SELECT EmployeeID, ManagerID, Title     FROM HumanResources.Employee     WHERE ManagerID IS NOT NULL   UNION ALL     SELECT  e.EmployeeID, e.ManagerID, e.Title     FROM HumanResources.Employee AS e     JOIN cte ON e.ManagerID = cte.EmployeeID ) SELECT EmployeeID, ManagerID, Title FROM cte; GO

H. 使用公用表表达式来有选择地执行 SELECT 语句中的递归操作

以下示例显示了为 ProductAssemblyID = 800 生产自行车所需的产品装配和部件层次结构。

复制代码

USE AdventureWorks; GO WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS (     SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,         b.EndDate, 0 AS ComponentLevel     FROM Production.BillOfMaterials AS b     WHERE b.ProductAssemblyID = 800           AND b.EndDate IS NULL     UNION ALL     SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,         bom.EndDate, ComponentLevel + 1     FROM Production.BillOfMaterials AS bom         INNER JOIN Parts AS p         ON bom.ProductAssemblyID = p.ComponentID         AND bom.EndDate IS NULL ) SELECT AssemblyID, ComponentID, Name, PerAssemblyQty, EndDate,         ComponentLevel FROM Parts AS p     INNER JOIN Production.Product AS pr     ON p.ComponentID = pr.ProductID ORDER BY ComponentLevel, AssemblyID, ComponentID; GO

I. 在 UPDATE 语句中使用递归 CTE

以下示例将直接或间接向 ManagerID 12 报告的所有雇员的 VacationHours 值增加 25%。公用表表达式将返回直接向 ManagerID 12 报告的雇员以及直接向这些雇员报告的雇员等的层次列表。只修改公用表表达式所返回的行。

复制代码

USE AdventureWorks; GO WITH DirectReports(EmployeeID, NewVacationHours, EmployeeLevel) AS (SELECT e.EmployeeID, e.VacationHours, 1   FROM HumanResources.Employee AS e   WHERE e.ManagerID = 12   UNION ALL   SELECT e.EmployeeID, e.VacationHours, EmployeeLevel + 1   FROM HumanResources.Employee as e   JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID ) UPDATE HumanResources.Employee SET VacationHours = VacationHours * 1.25 FROM HumanResources.Employee AS e JOIN DirectReports AS d ON e.EmployeeID = d.EmployeeID; GO

使用多个定位点和递归成员

以下示例使用多个定位点和递归成员来返回指定的人的所有祖先。创建了一个表，并在表中插入值，以建立由递归 CTE 返回的宗谱。

复制代码

-- Genealogy table IF OBJECT_ID('Person','U') IS NOT NULL DROP TABLE Person; GO CREATE TABLE Person(ID int, Name varchar(30), Mother int, Father int); GO INSERT Person VALUES(1, 'Sue', NULL, NULL); INSERT Person VALUES(2, 'Ed', NULL, NULL); INSERT Person VALUES(3, 'Emma', 1, 2); INSERT Person VALUES(4, 'Jack', 1, 2); INSERT Person VALUES(5, 'Jane', NULL, NULL); INSERT Person VALUES(6, 'Bonnie', 5, 4); INSERT Person VALUES(7, 'Bill', 5, 4); GO -- Create the recursive CTE to find all of Bonnie's ancestors. WITH Generation (ID) AS ( -- First anchor member returns Bonnie's mother.     SELECT Mother     FROM Person     WHERE Name = 'Bonnie' UNION -- Second anchor member returns Bonnie's father.     SELECT Father     FROM Person     WHERE Name = 'Bonnie' UNION ALL -- First recursive member returns male ancestors of the previous generation.     SELECT Person.Father     FROM Generation, Person     WHERE Generation.ID=Person.ID UNION ALL -- Second recursive member returns female ancestors of the previous generation.     SELECT Person.Mother     FROM Generation, Person     WHERE Generation.ID=Person.ID ) SELECT Person.ID, Person.Name, Person.Mother, Person.Father FROM Generation, Person WHERE Generation.ID = Person.ID; GO