SQL递归查询实战

数据库开发, SQL Server
目录

假设某公司的人员组织结构如下图：

数据库表：

CREATE TABLE ORG
(
	EMPID INT NOT NULL,
	EMPNAME VARCHAR(128) NOT NULL,
	MGRID INT NOT NULL
 );
GO
INSERT INTO ORG VALUES(1, 'Jack', 0);
INSERT INTO ORG VALUES(2, 'Mary', 1);
INSERT INTO ORG VALUES(3, 'Tom', 1);
INSERT INTO ORG VALUES(4, 'Ben', 2);
INSERT INTO ORG VALUES(5, 'John', 3);
INSERT INTO ORG VALUES(6, 'Emily', 3);
INSERT INTO ORG VALUES(7, 'Kate', 3);
INSERT INTO ORG VALUES(8, 'Mark', 6);
GO

需求一： 查询TOM管理的所有员工的名字

查询代码：

;WITH cte AS
(
	SELECT Empid,Empname,Mgrid
	FROM org
	WHERE Mgrid = (SELECT Empid FROM org WHERE Empname = 'Tom')
UNION ALL
	SELECT b.Empid,b.Empname,b.Mgrid
	FROM cte a INNER JOIN org b ON a.Empid = b.Mgrid
)
SELECT Empname FROM cte;

结果：

Empname
John
Emily
Kate
Mark

需求二：从下往上，查询Mark报告链

查询代码:

;WITH cte AS
(
	SELECT Empid,Empname,Mgrid
	FROM org
	WHERE Empid = (SELECT Mgrid FROM org WHERE Empname = 'Mark')
UNION ALL
	SELECT b.Empid,b.Empname,b.Mgrid
	FROM cte a INNER JOIN org b ON a.Mgrid = b.Empid
)
SELECT Empname FROM cte;

结果：

Empname
Emily
Tom
Jack

需求三：查询各人员所在层次

如Jack为1层，Tom为第二层，Mark为第四层

查询代码：

;WITH cte AS
(
	SELECT Empid,Empname,Mgrid, 1 [Level]
	FROM org
	WHERE Mgrid = 0
UNION ALL
	SELECT b.Empid,b.Empname,b.Mgrid,[Level]+1
	FROM cte a INNER JOIN org b ON a.Empid = b.Mgrid
)
SELECT Empname,[Level] FROM cte
ORDER BY [Level]

结果：

Empname Level
Jack 1
Mary 2
Tom 2
John 3
Emily 3
Kate 3
Ben 3
Mark 4

需求四：查询TOM管理 的人数

实际上就是在需求一的基础上求一个count(*):

;WITH cte AS
(
	SELECT Empid,Empname,Mgrid
	FROM org
	WHERE Mgrid = (SELECT Empid FROM org WHERE Empname = 'Tom')
UNION ALL
	SELECT b.Empid,b.Empname,b.Mgrid
	FROM cte a INNER JOIN org b ON a.Empid = b.Mgrid
)
SELECT COUNT(*) FROM cte;

需求五：求每个人的管理人数

-- 查询所有人的管理人数
;WITH cte AS
(
	--选择 org 表中所有的行，即所有的员工
	SELECT Empid,Empname,Mgrid
	FROM org
UNION ALL
	-- 对应前一次结果集的每一行，在新的结果集中为其经理插入一行
	SELECT b.Empid,b.Empname,b.Mgrid
	FROM cte a INNER JOIN org b ON a.Mgrid = b.Empid
)
-- 因为初始查询中每个员工都有初始行，所以最后结果要减去 1
SELECT Empname,COUNT(*)-1 [Mrg_Count]
FROM cte GROUP BY Empname

结果：

Empname Mrg_Count
Ben 0
Emily 1
Jack 7
John 0
Kate 0
Mark 0
Mary 1
Tom 4

reference

MSDN文档：使用公用表表达式的递归查询

伪代码和语义

递归 CTE 结构必须至少包含一个定位点成员和一个递归成员。以下伪代码显示了包含一个定位点成员和一个递归成员的简单递归 CTE 的组件。

WITH cte_name ( column_name [,...n] )
AS
(
	CTE_query_definition –- Anchor member is defined.
	UNION ALL
	CTE_query_definition –- Recursive member is defined referencing cte_name.
)
-- Statement using the CTE
SELECT *
FROM cte_name

递归执行的语义如下：

  1. 将 CTE 表达式拆分为定位点成员和递归成员。
  2. 运行定位点成员，创建第一个调用或基准结果集 (T0)。
  3. 运行递归成员，将 Ti 作为输入，将 Ti+1 作为输出。(注意输入只是Ti, 不是T0到Ti的合并结果，否则会有重复数据)
  4. 重复步骤 3，直到返回空集。
  5. 返回结果集。这是对 T0 到 Tn 执行 UNION ALL 的结果。