要在sql server 2000中实现递归查询,下面示例中给出的一个地区的一个递归示例,给出上级地区编码可查询所有子集地区信息和本级地区信息
create table #tabA
( PZoneId int,
ZoneId int)
create table #tabB
( PZoneId int,
ZoneId int)
insert into #tabA
select PZoneId,ZoneId from T_Zone
insert into #tabB
select PZoneId,ZoneId from T_Zone where ZoneId=1
declare @State bit
set @State=1
while @State=1
begin
delete #TabA from #TabA,#TabB where isnull(#TabA.PZoneId,'')=isnull(#TabB.PZoneId,'') And #TabA.ZoneId=#TabB.ZoneId
if Exists(select 1 from #TabA where PZoneId in (select ZoneId from #tabB))
insert into #TabB select * from #TabA where PZoneId in (select ZoneId from #tabB)
else
set @State=0
end
select * from T_Zone where ZoneId in (select ZoneID from #TabB)