SQLSERVER2005 中树形数据的递归查询

SQLServer 2005提供了递归查询（Recursive Query）功能，可以用来查询树形数据。常用的场景是查询组织机构、产品分类、地区等具有层级关系的数据。

递归查询的语法如下：

WITH CTE AS (
  -- Anchor member
  SELECT ...
  UNION ALL
  -- Recursive member
  SELECT ...
  FROM CTE
  WHERE ...
)
SELECT ...
FROM CTE

其中，AN(X)CHOR MEMBER是递归的起点，相当于树的根节点，不能递归，但是必须包含在CTE中；RECURSIVE MEMBER是递归的迭代过程，相当于树的子节点，可以递归，必须引用CTE本身，直到满足递归停止的条件。

下面的示例演示如何使用递归查询来查询组织机构树，假设有以下表结构：

CREATE TABLE organizations (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  parent_id INT
);

其中，id是组织机构的唯一标识符，name是组织机构的名称，parent_id是组织机构的上级节点的id。

首先，将根节点的id为0的组织机构（假设名称为根节点）插入到表中：

INSERT INTO organizations (id, name, parent_id)
VALUES (0, '根节点', NULL);

然后，插入一些子节点数据：

INSERT INTO organizations (id, name, parent_id)
VALUES (1, '子节点1', 0);
INSERT INTO organizations (id, name, parent_id)
VALUES (2, '子节点2', 0);
INSERT INTO organizations (id, name, parent_id)
VALUES (3, '子节点3', 1);
INSERT INTO organizations (id, name, parent_id)
VALUES (4, '子节点4', 1);
INSERT INTO organizations (id, name, parent_id)
VALUES (5, '子节点5', 2);
INSERT INTO organizations (id, name, parent_id)
VALUES (6, '子节点6', 2);

接下来，使用递归查询来查询组织机构树：

WITH CTE AS (
  SELECT id, name, parent_id
  FROM organizations
  WHERE id = 0
  UNION ALL
  SELECT o.id, o.name, o.parent_id
  FROM organizations o
  INNER JOIN CTE ON o.parent_id = CTE.id
)
SELECT *
FROM CTE;

上述代码中，WHERE子句中指定根节点的id为0，作为递归查询的起点。UNION ALL将根节点也包含在递归查询中。第一个SELECT是递归的起点，第二个SELECT是递归的迭代过程，从上一层级的节点查询下一层级的子节点。

除了查询整个树，还可以查询指定节点的子树。例如，查询id为1的节点的子树：

WITH CTE AS (
  SELECT id, name, parent_id
  FROM organizations
  WHERE id = 1
  UNION ALL
  SELECT o.id, o.name, o.parent_id
  FROM organizations o
  INNER JOIN CTE ON o.parent_id = CTE.id
)
SELECT *
FROM CTE;

上述代码中，WHERE子句指定id为1的节点为起点，查询其子节点。其他部分和查询整个树的代码相同。