在数据库管理中，经常需要处理层级关系，例如组织结构、分类体系等。在SQL Server中，可以使用递归查询来解决这类问题。本文将介绍如何使用Common Table Expressions（CTE）来实现对特定表的层级关系的查询，包括查找特定行的所有可能的父节点和子节点，以及如何展示这些节点。
假设有一个用户类型表（UserType），其中每一行可以有一个父节点或者没有父节点（即parentId为NULL）。目标是实现以下功能：
首先，定义UserType表的结构：
CREATE TABLE UserType(
Id BIGINT NOT NULL,
Name VARCHAR(100) NOT NULL,
ParentId BIGINT NULL
);
接下来，填充一些数据到UserType表中：
DECLARE @maxCount BIGINT, @count BIGINT, @parentId BIGINT;
SET @maxCount = 10;
SET @count = 1;
WHILE @count <= @maxCount
BEGIN
IF @count = 1 SET @parentId = NULL;
ELSE SET @parentId = @count - 1;
INSERT INTO UserType(Id, Name, ParentId) VALUES (@count, 'User_' + CONVERT(VARCHAR(400), @count), @parentId);
SET @count = @count + 1;
END
为了实现递归查询，需要使用CTE。以下是使用CTE进行基本的行生成示例：
WITH Hierarchy(ChildId, ChildName, Generation, ParentId) AS (
SELECT Id, Name, 0, ParentId FROM UserType AS FirstGeneration WHERE ParentId IS NULL
UNION ALL
SELECT NextGeneration.Id, NextGeneration.Name, Parent.Generation + 1, Parent.ChildId
FROM UserType AS NextGeneration
INNER JOIN Hierarchy AS Parent ON NextGeneration.ParentId = Parent.ChildId
)
SELECT * FROM Hierarchy OPTION (MAXRECURSION 32767);
以下是查询特定行（例如Id=5）的所有可能的父节点的示例：
DECLARE @id BIGINT;
SET @id = 5;
WITH tblParent AS (
SELECT * FROM UserType WHERE Id = @id
UNION ALL
SELECT UserType.* FROM UserType JOIN tblParent ON UserType.Id = tblParent.ParentId
)
SELECT * FROM tblParent WHERE Id <> @id OPTION (MAXRECURSION 32767);
以下是查询特定行（例如ParentId=5）的所有可能的子节点的示例：
DECLARE @userTypeId BIGINT;
SET @userTypeId = 5;
WITH tblChild AS (
SELECT * FROM UserType WHERE ParentId = @userTypeId
UNION ALL
SELECT UserType.* FROM UserType JOIN tblChild ON UserType.ParentId = tblChild.Id
)
SELECT * FROM tblChild OPTION (MAXRECURSION 32767);
以下是以特定分隔符（例如'.'）显示所有可能的父节点的示例：
WITH Hierarchy(ChildId, ChildName, ParentId, Parents) AS (
SELECT Id, Name, ParentId, CAST('.' AS VARCHAR(MAX)) FROM UserType AS FirstGeneration WHERE ParentId IS NULL
UNION ALL
SELECT NextGeneration.Id, NextGeneration.Name, Parent.ChildId,
CAST(CASE WHEN Parent.Parents = '.' THEN (CAST(NextGeneration.ParentId AS VARCHAR(MAX))) ELSE (Parent.Parents + '.' + CAST(NextGeneration.ParentId AS VARCHAR(MAX))) END AS VARCHAR(MAX))
FROM UserType AS NextGeneration
INNER JOIN Hierarchy AS Parent ON NextGeneration.ParentId = Parent.ChildId
)
SELECT * FROM Hierarchy OPTION (MAXRECURSION 32767);
以下是以特定分隔符（例如'.'）显示所有可能的子节点的示例：
WITH Hierarchy(ChildId, ChildName, ParentId, Childs) AS (
SELECT Id, Name, ParentId, CAST('.' AS VARCHAR(MAX)) FROM UserType AS LastGeneration WHERE Id NOT IN (SELECT COALESCE(ParentId, 0) FROM UserType)
UNION ALL
SELECT PrevGeneration.Id, PrevGeneration.Name, PrevGeneration.ParentId,
CAST(CASE WHEN Child.Childs = '.' THEN (CAST(Child.ChildId AS VARCHAR(MAX))) ELSE (Child.Childs + '.' + CAST(Child.ChildId AS VARCHAR(MAX))) END AS VARCHAR(MAX))
FROM UserType AS PrevGeneration
INNER JOIN Hierarchy AS Child ON PrevGeneration.Id = Child.ParentId
)
SELECT * FROM Hierarchy OPTION (MAXRECURSION 32767);
在所有之前的查询中，使用了如下语法来指定递归的最大次数：
OPTION (MAXRECURSION 32767)
OPTION (MAXRECURSION 0)