使用SQLSERVER 2005/2008 递归CTE查询树型结构的方法

标题：使用SQLSERVER 2005/2008 递归CTE查询树型结构的方法

CTE（Common Table Expressions）是SQL Server 2005引进的一个强大的查询语句，它可以非常方便地实现树型结构的查询。递归CTE查询树型结构的方法比较灵活，可以适用于不同种类的树型结构，包括文件系统、组织结构、产品分类等等。

一、常规格式

在SQL Server中，CTE的语法格式如下：

WITH cte_name (column_list) AS
(
  --recursive query 1
  SELECT column_list FROM table1 WHERE condition
  UNION ALL
  --recursive query 2
  SELECT column_list FROM table1 JOIN cte_name ON condition
)
SELECT column_list FROM cte_name

其中，

• cte_name为CTE的名称，可以为任何合法的标识符；
• column_list为CTE要返回的列的列表；
• (recursive query 1)和(recursive query2)为递归查询语句，通过UNION ALL关键字连接起来；
• condition为递归查询条件，用来连接子查询和母查询的关系。

二、示例讲解

1. 手动建立树型表

下面以一个手动建立的树型表tree为例，此树型表无任何直接递归关系：

CREATE TABLE tree (
  id INT PRIMARY KEY,
  name VARCHAR(20),
  parent_id INT
);

INSERT INTO tree VALUES (1, 'root', NULL);
INSERT INTO tree VALUES (2, 'node-1', 1);
INSERT INTO tree VALUES (3, 'node-2', 1);
INSERT INTO tree VALUES (4, 'node-1-1', 2);
INSERT INTO tree VALUES (5, 'node-1-2', 2);
INSERT INTO tree VALUES (6, 'node-2-1', 3);
INSERT INTO tree VALUES (7, 'node-2-2', 3);
INSERT INTO tree VALUES (8, 'node-1-1-1', 4);
INSERT INTO tree VALUES (9, 'node-1-1-2', 4);
INSERT INTO tree VALUES (10, 'node-1-2-1', 5);
INSERT INTO tree VALUES (11, 'node-1-2-2', 5);
INSERT INTO tree VALUES (12, 'node-2-1-1', 6);
INSERT INTO tree VALUES (13, 'node-2-1-2', 6);
INSERT INTO tree VALUES (14, 'node-2-2-1', 7);
INSERT INTO tree VALUES (15, 'node-2-2-2', 7);

这组数据表示了一棵十五个节点的树：

root
├─ node-1
│  ├─ node-1-1
│  │  ├─ node-1-1-1
│  │  └─ node-1-1-2
│  └─ node-1-2
│     ├─ node-1-2-1
│     └─ node-1-2-2
└─ node-2
   ├─ node-2-1
   │  ├─ node-2-1-1
   │  └─ node-2-1-2
   └─ node-2-2
      ├─ node-2-2-1
      └─ node-2-2-2

现在我们想要查询node-1-1的所有祖先节点。使用递归CTE可以非常方便地实现：

WITH cte AS (
  SELECT name, id, parent_id FROM tree
  WHERE name = 'node-1-1'
  UNION ALL
  SELECT t.name, t.id, t.parent_id FROM tree t
  JOIN cte ON t.id = cte.parent_id
)
SELECT name FROM cte;

输出结果为：

name        
------------
node-1-1    
node-1      
root        
(3 rows affected)

查询语句的思路如下：

• 首先从tree表中选择name为node-1-1的一行，作为递归查询的起点，结果包含name、id和parent_id三列；
• 然后在tree表中，匹配id为上一步结果中的parent_id的行，作为下一轮递归查询的起点，结果同样包含name、id和parent_id三列；
• 逐层递归，直到找到parent_id为NULL（根节点）的行为止。

递归CTE的语法可以让我们非常方便地查询树型结构，而不需要借助其他工具。

2. 使用SYS.DM_EXEC_DESCRIBE_FIRST_RESULT_SET获取查询结果的元数据

在使用递归CTE查询树型结构时，查询结果的列数和列名是很重要的信息。可以使用SYS.DM_EXEC_DESCRIBE_FIRST_RESULT_SET元数据函数，来获取查询结果的元数据，包括列数、列名和数据类型。

假设有如下的树型表：

CREATE TABLE department
(
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50),
    parent_dept_id INT NULL
);

INSERT INTO department VALUES (1, '总公司', NULL);
INSERT INTO department VALUES (2, '财务部', 1);
INSERT INTO department VALUES (3, '人力资源部', 1);
INSERT INTO department VALUES (4, '财务一部', 2);
INSERT INTO department VALUES (5, '财务二部', 2);
INSERT INTO department VALUES (6, '技术部', 1);
INSERT INTO department VALUES (7, '研发部', 6);
INSERT INTO department VALUES (8, '测试部', 6);

现在，我们要查询某个部门的所有子部门以及所有子部门的员工数，要求查询结果包含3列：部门名称、员工数、级别（其中根节点的级别为0）。

使用递归CTE可以轻松地实现此功能：

WITH cte AS
(
    -- 递归查询的起点
    SELECT dept_name AS [Name], 0 AS [Level], dept_id AS [ParentDeptID], 0 AS [EmployeeCount]
    FROM department
    WHERE dept_id = 1
    UNION ALL

    -- 递归查询子部门
    SELECT cte.[Name], cte.[Level] + 1, d.parent_dept_id AS [ParentDeptID], SUM(e.employee_count) AS [EmployeeCount]
    FROM department d
    INNER JOIN cte ON d.parent_dept_id = cte.ParentDeptID
    INNER JOIN (SELECT dept_id, COUNT(1) AS employee_count FROM employee GROUP BY dept_id) e ON d.dept_id = e.dept_id
    GROUP BY d.parent_dept_id, cte.[Name], cte.[Level]
)
SELECT [Name], [EmployeeCount], [Level]
FROM cte
ORDER BY [Level], [Name];

查询结果为：

Name                  EmployeeCount      Level
--------------------  ----------------  -----
总公司                 11                 0
财务部                5                 1
人力资源部             0                 1
技术部                6                 1
测试部                4                 2
研发部                2                 2
财务一部               1                 2
财务二部               4                 2

在此查询语句中，使用了内联子查询和分组聚合函数，用于计算每个部门的员工数。同时，递归查询语句内部连接了department表和cte临时表，用于查询每个部门的直接子部门。

通过使用CTE和SYS.DM_EXEC_DESCRIBE_FIRST_RESULT_SET元数据函数，可以非常方便地实现树型结构的查询，而不需要编写复杂的代码。可以根据实际需求和查询场景，进一步优化查询语句，以达到更好的性能和查询效果。