sql server 2005中使用with实现递归的方法

利用WITH和递归公用表达式(Common Table Expressions, CTE)，可以在SQL Server 2005中使用递归查询。递归查询是一种常见的数据查询方式，在处理层级结构或树状数据时，非常有用。下面是实现递归查询的详细步骤：

1. 创建递归公用表达式，并定义初始查询语句。
   以查询公司组织架构为例，假设公司存在一个员工表格，表格结构如下：

CREATE TABLE employees (
    id int PRIMARY KEY,
    name varchar(50) NOT NULL,
    supervisor_id int NULL
);

在该表中，每个员工都有唯一的ID，姓名和主管的ID。如果员工没有主管，则Supervisor_id为NULL。

下面是一条递归查询语句的例子：

WITH theCTE (id, name, supervisor_id, depth) AS
(
SELECT id, name, supervisor_id, 0 AS depth
FROM employees
WHERE supervisor_id IS NULL
UNION ALL
SELECT e.id, e.name, e.supervisor_id, depth + 1 AS depth
FROM employees AS e
INNER JOIN theCTE ON theCTE.id = e.supervisor_id
)
SELECT id, name, depth
FROM theCTE

在这个查询语句中，我们向WITH过早定义的递归公用表达式中添加记录。表达式的名称为theCTE，包含四个列：ID, 名称, 主管ID和深度。查询语句由两个部分组成：首先，我们通过与NULL比较，从顶层主管开始，选择员工表中所有顶层员工记录，并将深度depth设置为0。其次，我们使用UNION ALL运算符作为递归部分，它将每个员工作为子查看彼此之间的关系。我们可以使用INNER JOIN将递归子查询(theCTE)与员工表格(e)进行连接，以便在每一级深度时为更高的层级加入员工。每个递归子查询的深度(depth)都比上一级递归查询的深度深1。

1. 执行递归查询语句。
   使用上述查询语句，我们可以查询公司组织架构中每个员工的深度，结果如下：

id  | name         | depth
----+--------------+------
1   | John Doe     | 0
2   | Jane Smith   | 0
3   | Bob Johnson  | 1
4   | Jerry Smith  | 2
5   | Dave Jackson | 1
6   | Sarah Flann  | 1
7   | Nancy Crane  | 3

在这个结果中，每个员工的深度是指他们与顶级主管之间的层级数。

1. 注意事项：
2. 递归查询必须始终包含一个UNION ALL分隔符。
3. 递归表达式必须引用自身。
4. 第一条查询必须从一组初始化记录中开始。
5. SELECT语句从递归表达式中选择结果。

下面是另一个示例，它使用递归查询在树状结构中查找所有父子关系：

WITH tree(root, id, name, path) AS
(
SELECT CAST(NULL AS int), id, name, CAST(name AS varchar(100)) AS path
FROM nodes
WHERE parent_id IS NULL
UNION ALL
SELECT CAST(t.id AS int), n.id, n.name, CAST(t.path + ' > ' + n.name AS varchar(100))
FROM nodes AS n
INNER JOIN tree AS t ON n.parent_id = t.id
)
SELECT id, name, path
FROM tree

在这个示例中，我们使用连接Table nodes，该表包含有关树状结构中节点的信息，每个节点包括一个ID，一个名称和一个父节点ID。首先，我们选择顶级节点，如同上一个示例中的第一部分所示。我们使用CAST函数将根(? NULL)转换为int类型。其次，我们使用INNER JOIN连接树的相邻层级，如第一部分的第二部分所示。这将连接t.id和n.parent_id组成的递归子查询。在每个递归层级上，我们都选择一个唯一的ID，一个名称以及从当前节点到顶级节点的路径(path)。最后，我们从递归CTE中返回结果。

这是一些关于如何在SQL Server 2005中使用WITH表达式进行递归查询的示例和说明。以上例子可以应用于许多其他类型的查询，包括对文本字符串和时间轴数据的递归查询。这些查询的实现方式不太相同，但使用递归CTE的基本原理是相同的。