SQLserver2008使用表达式递归查询

下面是“SQL server 2008使用表达式递归查询”的完整攻略。

什么是表达式递归查询

表达式递归查询是一种使用递归方式查询数据的方法。它与常规递归的不同之处在于它使用了SQL Server的WITH语句，这使得它更容易理解而且性能更好。在这种类型的查询中，一个查询使用自身的输出来生成下一个查询的输入，这样就可以逐步构造出一个结果集。

使用表达式递归查询的步骤

使用表达式递归查询的步骤如下：

1. 数据库准备：首先创建一组表，并填充具有嵌套关系以演示递归查询的功能。

CREATE TABLE Department
(
    DeptID INT PRIMARY KEY,
    DeptName VARCHAR(30),
    ParentDeptID INT
);

INSERT INTO Department VALUES (1,'总裁办',NULL);
INSERT INTO Department VALUES (2,'研发部',1);
INSERT INTO Department VALUES (3,'市场部',1);
INSERT INTO Department VALUES (4,'财务部',1);
INSERT INTO Department VALUES (5,'软件研发部',2);
INSERT INTO Department VALUES (6,'硬件研发部',2);
INSERT INTO Department VALUES (7,'销售部',3);
INSERT INTO Department VALUES (8,'财务处',4);

1. 编写表达式递归查询：

WITH RECURSIVE QueryResult(DeptID,DeptName,ParentDeptID,Level)
AS
(
SELECT DeptID, DeptName, ParentDeptID, 0 as Level
FROM Department
WHERE ParentDeptID IS NULL
UNION ALL
SELECT t1.DeptID, t1.DeptName, t1.ParentDeptID, t2.Level+1
FROM Department t1, QueryResult t2
WHERE t1.ParentDeptID = t2.DeptID
)
SELECT DeptID,DeptName,ParentDeptID,Level FROM QueryResult;

在这个查询中，子查询QueryResult是一个递归的表达式。在第一步中，我们选择了具有NULL ParentDeptID的行，这是这个表达式递归的起点。在第二个查询中，我们使用了自身的输出来构建下一个输入。

这个表达式递归查询使用了4个字段：DeptID，DeptName，ParentDeptID和Level。在查询中，我们用0作为起始Level。在每一次递归中，Level加1，这让我们能够跟踪每个部门的层级。

示例

下面是两个示例说明：

示例1

这个示例展示了如何显示每个部门及其所有下级部门的信息。

WITH RECURSIVE QueryResult(DeptID,DeptName,ParentDeptID,Level)
AS
(
SELECT DeptID, DeptName, ParentDeptID, 0 as Level
FROM Department
WHERE ParentDeptID IS NULL
UNION ALL
SELECT t1.DeptID, t1.DeptName, t1.ParentDeptID, t2.Level+1
FROM Department t1, QueryResult t2
WHERE t1.ParentDeptID = t2.DeptID
)
SELECT DeptID,DeptName,ParentDeptID,Level FROM QueryResult;

这个查询的结果如下：

DeptID  DeptName           ParentDeptID  Level
1       总裁办              NULL          0
2       研发部              1             1
5       软件研发部          2             2
6       硬件研发部          2             2
3       市场部              1             1
7       销售部              3             2
4       财务部              1             1
8       财务处              4             2

示例2

这个示例展示了如何找到指定部门的所有下级部门。

WITH RECURSIVE QueryResult(DeptID,DeptName,ParentDeptID,Level)
AS
(
SELECT DeptID, DeptName, ParentDeptID, 0 as Level
FROM Department
WHERE DeptID = 2
UNION ALL
SELECT t1.DeptID, t1.DeptName, t1.ParentDeptID, t2.Level+1
FROM Department t1, QueryResult t2
WHERE t1.ParentDeptID = t2.DeptID
)
SELECT DeptID,DeptName,ParentDeptID,Level FROM QueryResult;

这个查询的结果如下：

DeptID  DeptName           ParentDeptID  Level
2       研发部              1             0
5       软件研发部          2             1
6       硬件研发部          2             1

这个查询选择了DeptID为2的行作为起点，然后找到了所有下级部门的信息。