sqlServer递归查询 with as

sqlServer递归查询 with as

with
district as
(
– 获得第一个结果集，并更新最终结果集
select * from t_tree where node_name= N’辽宁省’
union all
– 下面的select语句首先会根据从上一个查询结果集中获得的id值来查询parent_id
– 字段的值，然后district就会变当前的查询结果集，并继续执行下面的select 语句
– 如果结果集不为null，则与最终的查询结果合并，同时用合并的结果更新最终的查
– 询结果；否则停止执行。最后district的结果集就是最终结果集。
select a.* from t_tree a, district b
where a.parent_id = b.id
)
select * from district

with
district as
(
select * from t_tree where node_name= N’辽宁省’
union all
select a.* from t_tree a, district b
where a.parent_id = b.id
),
district1 as
(
select a.* from district a where a.id in (select parent_id from district)
)
select * from district1

with district as (
select * from t_tree where node_name= N’辽宁省’
union all
select a.* from t_tree a, district b
where a.id = b.id – 故意制造无限循环
)
select * from district
OPTION (MAXRECURSION 5) – 限制特定语句所允许的递归级数，以防止出现无限循环

if exists (
select *
from sys.objects
where name = ‘fn_Test’
and type in (‘FN’ ,‘TF’)
)
drop function dbo.fn_Test;
go

create function dbo.fn_Test
(
@nodename varchar(50)
)
returns @result table (
id int not null
,node_name varchar(50) null
,parent_id int not null
,[description] varchar(255) null
)
as
begin
;with district as (
select *
from t_tree where node_name = @nodename

			 union all

			select a.* 
			from   t_tree a, district b
				   where a.parent_id = b.id
),
district1 as ( 
	select *
	from   district a 
			where a.id in (select parent_id
							from   district)
)

insert into @result
  (
    id
   ,node_name
   ,parent_id
   ,[description]
  )
select id
      ,node_name
      ,parent_id
      ,[description]
from   district1;

return ;

end;
go