MSSQL 查询所有节点及其所有子节点的函数

核心提示：set @id = 0while exists(select 1 from area where order_by = 2 and id > @id)beginset @id = (select min(id) from area where order_by = 2 and id > @id)insert

set @id = 0
while exists(select 1 from area where order_by = 2 and id > @id)
begin
set @id = (select min(id) from area where order_by = 2 and id > @id)
insert into tmp(Name ,order_by ,father_ID) select a.name,a.order_by ,a.father_id from area a , f_cid(@id) b where a.id = b.id order by a.id
end
end
go
exec my_proc

--从临时表提取数据并显示
select case when order_by = 2 then name
when order_by = 1 then ' ' + name
when order_by = 0 then ' ' + name
end name
from tmp order by id

drop function f_cid
drop proc my_proc
drop table area , tmp

/*
name
--------------
广东省
东莞市
广州市
天河区
四川省
绵阳市
湖北省
武汉市
汉口区
随州市

（所影响的行数为 10 行）