SQL 递归查询初体验

2013-11-03

需求：

有如下一张职员上司表，要求给一个职工名，返回他直接或间接管理的所有职工名，写一个函数实现。

分析：

员工的下属也可能是其他员工的上司，即下属也有下属，这样一直找，直到某员工没有下属为止。SQL SERVER 从 2005 起，开始支持 CTE 表达式，可以帮助我们更好的处理这样的一类递归问题。为了体现出递归的简洁，我们先给出非递归即迭代解法：

if object_id('dbo.findEmp','TF') is not null
	drop function dbo.findEmp
go
/*寻找直接或间接下属*/
create function findEmp
(
@mgr varchar(20)
)
returns @empl table(name varchar(20)) -- 所有结果的union all
as
begin
	declare @newemp table(name varchar(20)) -- 新产生的结果 (或上次递归的结果)
	declare @temp table(name varchar(20))   -- 临时结果    (本次递归的结果)

	-- 第一级
	insert into @newemp
		select employee_name from manager
		where manager_name = @mgr

	while exists(select 1 from @newemp)
		begin
			insert into @empl
				select name from @newemp
			--第N级（第N次迭代时）
			insert into @temp
				select a.employee_name
				from manager a inner join @newemp b
				on a.manager_name = b.name
			except  -- 管理关系存在的循环时（非正常情况）也能正常工作
				select name from @empl

			delete from @newemp
			insert into @newemp select * from @temp
			delete from @temp
		end
	return
end

/*
-- 测试
select * from dbo.FindEmp('Jones')

name
--------------------
Duarte
Estovar
Basinsky
Corbin
Alon

(5 行受影响)
*/

再看看sql递归查询是如何实现的：

if object_id('dbo.findEmp_Plus') is not null
	drop function dbo.findEmp_Plus
go

create function findEmp_Plus
(
	@mgr varchar(20)
)
returns table
as
return
(
	with emp as
	(
		select employee_name
		from manager
		where manager_name = @mgr
	union all
		select b.employee_name
		from emp a inner join manager b
		on a.employee_name = b.manager_name
	)
	select * from emp
)
go

/*
-- 测试
select * from dbo.FindEmp_plus('Jones')

name
--------------------
Duarte
Estovar
Basinsky
Corbin
Alon

(5 行受影响)
*/

是不是直觉递归简洁巧妙多了！！！

递归查询是很简洁，但不太好理解，其实可以借助上面的迭代来理解，就本例求Jones的所有下属而言：

第一步生成cte中的原始数据，即Jones的直接下属，Duarte 和 Estovar；

第二步生成 Duarte 和 Estovar 的直接下属，Basinsky 和 Corbin；

第三步生成 Basinsky 的直接下属Alon；

第四步结果为空，递归结束，结果是各步生成数据的union.

更多例子，请看我的另一篇文章：SQL 递归查询实战

官方介绍，请看MSDN文档：使用公用表表达式的递归查询 (含执行过程举例解析)