SQL Server递归查询，3分钟明白CTE（公用表达式）

本文阅读导航 展开

实际项目中，经常会遇到带有层级关系的数据结构，把这些数据保存到数据库里面，形成一个自递归表的结构，对于这种表的查询，SQL Server递归查询可以使用CTE（公用表达式）很方便的进行递归查询。

问题

许多组织都有某种类型的业务流程层次结构（树状数据结构）。对于大型组织，层次结构可能变得非常复杂且庞大，因此在RDBMS中构建层次结构是一项繁琐的任务。我们必须创建视图，游标等，但是在SQL Server递归查询中使用CTE是查询基于层次结构数据的解决方案，在本技巧中，我将向您展示如何进行。

解决方法

公用表达式（CTE）有两种类型，递归和非递归。在本技巧中，我们将通过示例了解递归CTE的工作方式。

递归CTE可以分为三个部分：

定位查询：这是第一个执行的语句。该查询将提供CTE的基础数据。
分隔符：这是中间部分，通常我们使用UNION ALL和少量其它运算符。
递归查询：这是CTE查询核心部分，它通过递归引用相同的CTE。

让我们创建一个CTE示例

让我们以组织（org）图表为例。在此示例中，组织结构图将从“ CEO”开始，到“采购部门”结束。每个部门/人员都作为节点链接到前任。让我们看看如何在SQL Server中使用CTE来实现这一目标。我们还会演示基于使用CTE进行的递归查询。

创建一个简单的表：

IF OBJECT_ID ('MyDepartment','U') IS NOT NULL
    DROP TABLE MyDepartment;
GO 
 
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[MyDepartment]
(
   [DepartmentID] [smallint] NOT NULL,
   [DepartmentName] [nvarchar](30) NOT NULL,
   [ParentID] [nvarchar](40) NULL,
   CONSTRAINT [PK_DepartmentID] PRIMARY KEY CLUSTERED ( [DepartmentID] ASC )
   WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] 
GO

MyDepartment表创建好了之后，我们需要添加一些测试数据。可以执行以下插入语句，将数据插入“ MyDepartment”表中。

INSERT INTO [dbo].[MyDepartment] ([DepartmentID],[DepartmentName],[ParentID])
VALUES
   ('1','CEO',null),
   ('2','President','1'),
   ('3','Chairman','1'),
   ('4','Vice President','2'),
   ('5','Associate Vice President','4'),
   ('6','Senior Manager','4'),
   ('7','Delivery Manager','4'),
   ('8','Program Manager','4'),
   ('9','Project Manager','5'),
   ('10','Planning Manager','5'),
   ('11','Execution Manager','5'),
   ('12','Project Leader','6'),
   ('13','Project Planner','6'),
   ('14','Senior Project Lead','12'),
   ('15','Team Lead','12'),
   ('16','Sprint Lead','12'),
   ('17','Statistics Department','6'),
   ('18','Logistics Department','6'),
   ('19','Sales Account','7'),
   ('20','Customer Service','7'),
   ('21','Product Support B','8'),
   ('22','Sales Department','21'),
   ('23','Purchase Department','21'),
   ('24','Group Manager','8'),
   ('25','Overseas Department','24'),
   ('26','Domestic Department','24');
go

最顶层树的ParentID设置为NULL，表示顶层部门（根）。现在，我们可以使用公用表达式编写查询语句实现对层次结构结构数据的遍历。

我们需要查询CEO领导下的整个组织结构。以下使用SQL Server 的 CTE表达式代码为我们提供查询：

WITH OrgTree (DepartmentID, DepartmentName, ParentID, Tree)
AS
(
   SELECT DepartmentID, DepartmentName, ParentID , 0 AS Tree 
   FROM MyDepartment
   WHERE ParentID IS NULL
   UNION ALL
   SELECT MyDepartment.DepartmentID, MyDepartment.DepartmentName, MyDepartment.ParentID , OrgTree.Tree + 1
   FROM MyDepartment
   JOIN OrgTree ON MyDepartment.ParentID = OrgTree.DepartmentID
)
 
SELECT * FROM OrgTree ORDER BY Tree

以下是上述查询语句执行的输出结果：

定位查询

创建表只是其中的一部分，在示例中插入数据是另一回事。最为重要的部分是使用WITH子句实现CTE。对于我们的示例，CTE的名称被命名为 “OrgTree”。CTE中的第一个选择用于提取树的第一个节点“CEO”，并且将父ID设置为NULL。下面的查询将获得我们示例中的第一个节点。

SELECT DepartmentID, DepartmentName, ParentID , 0 AS Tree 
FROM MyDepartment
WHERE ParentID IS NULL

下面我解释数据。左侧具有父数据，右侧具有子数据。如果您注意到DepartmentID 1（左侧）是DepartmentID（右侧）2和3的父级。如果进一步查看，每个DepartmentID都与子表中的ParentID相关联。下面是上面生成的查询的图像表示。箭头连接DepartmentID和ParentID供我们参考。

分隔符和递归查询

接下来的是CTE的INNER JOIN，其中介绍了递归，是自引用。CTE语句块里面INNER JOIN通过使用别名OrgTree 将“ MyDepartment”表分为两部分来检索数据，并进行内（inner）接接并创建CTE供我们查询。

SELECT MyDepartment.DepartmentID, MyDepartment.DepartmentName, MyDepartment.ParentID , OrgTree.Tree + 1
FROM MyDepartment
JOIN OrgTree ON MyDepartment.ParentID = OrgTree.DepartmentID

查询设计器

下面的查询设计器屏幕打印可供我们在设计器中查看查询。右侧是CTE-“ OrgTree”，由于CTE将在执行后创建，因此如果您注意到“ MyDepartment”表包含该列和INNER JOIN引用，则查询设计器将不显示该列。

MAXRECURSION（最大递归层级/深度）

MAXRECURSION是指在数据层次较深的情况下，你可以通过参数设置最大递归层级，当涉及使用CTE时，面临的问题之一是在形成CTE时出现无限循环。通常，当父查询和子查询返回相同或相等的值时，CTE可能进入无限循环，而事务可能进入无限循环。为了避免这种情况，可以在CTE SELECT命令的末尾使用一个选项子句，其关键字为MAXRECURSION和行数。使用0没有限制，但是在我们的示例中，我使用的值为10。

WITH OrgTree (DepartmentID, DepartmentName, ParentID, Tree)
AS
(
   SELECT DepartmentID, DepartmentName, ParentID, 0 AS Tree 
   FROM MyDepartment
   WHERE ParentID IS NULL
   UNION ALL
   SELECT MyDepartment.DepartmentID, MyDepartment.DepartmentName, MyDepartment.ParentID, OrgTree.Tree + 1
   FROM MyDepartment
   JOIN OrgTree ON MyDepartment.ParentID = OrgTree.DepartmentID
)
SELECT * FROM OrgTree OPTION (MAXRECURSION 10)

查询示例

你可以下面的示例查询来查找数据，并查看是否可以提出其他方案以及如何查询数据。

-- return everyone under Program Manager (ParentID = 8)
WITH OrgTree (DepartmentID, DepartmentName, ParentID, Tree)
AS
(
   SELECT DepartmentID, DepartmentName, ParentID , 0 AS Tree 
   FROM MyDepartment
   WHERE ParentID = 8
   UNION ALL
   SELECT MyDepartment.DepartmentID, MyDepartment.DepartmentName, MyDepartment.ParentID , OrgTree.Tree + 1
   FROM MyDepartment
   JOIN OrgTree ON MyDepartment.ParentID = OrgTree.DepartmentID
)
SELECT * FROM OrgTree;


-- return Vice President (DepartmentID = 4) and direct reports (ParentID = 4)
WITH OrgTree (DepartmentID, DepartmentName, ParentID, Tree)
AS
(
   SELECT DepartmentID, DepartmentName, ParentID , 0 AS Tree 
   FROM MyDepartment
   WHERE DepartmentID = 4
   UNION ALL
   SELECT MyDepartment.DepartmentID, MyDepartment.DepartmentName, MyDepartment.ParentID , OrgTree.Tree + 1
   FROM MyDepartment
   JOIN OrgTree ON MyDepartment.ParentID = OrgTree.DepartmentID
   WHERE MyDepartment.ParentID = 4
)
SELECT * FROM OrgTree;

  
-- return everyone above Senior Manager (DepartmentID = 6)
WITH OrgTree(DepartmentName,ParentID,ReportsTo)AS
(
   SELECT T1.DepartmentName,T2.DepartmentID,T2.DepartmentName 
   FROM MyDepartment T1
   INNER JOIN MyDepartment T2 ON T1.ParentID=T2.DepartmentID 
   WHERE T1.DepartmentID=6
   UNION ALL
   SELECT OT.ReportsTo,T2.DepartmentID,T2.DepartmentName
   FROM OrgTree OT
   INNER JOIN MyDepartment T1 ON OT.ParentID=T1.DepartmentID
   INNER JOIN MyDepartment T2 ON T1.ParentID=T2.DepartmentID
)
SELECT * FROM OrgTree;


-- return list with of people with no direct reports
WITH OrgTree(ParentID, DepartmentID, DepartmentName, DepartmentLevel) AS 
(
    SELECT ParentID, DepartmentID, DepartmentName, 0 AS DepartmentLevell
    FROM MyDepartment 
    WHERE ParentID IS NULL
    UNION ALL
    SELECT e.ParentID, e.DepartmentID, e.DepartmentName,  DepartmentLevel + 1
    FROM MyDepartment AS e
    INNER JOIN OrgTree AS d ON e.ParentID = d.DepartmentID 
)
SELECT * FROM OrgTree WHERE DepartmentLevel = 5;