天天SQL(查询所有子节点及其函数的练习)

------tb
create table tb(id varchar(3) , pid varchar(3) , name varchar(10))

insert into tb values('001' , null  , '河南省')
insert into tb values('002' , '001' , '洛阳市')
insert into tb values('003' , '001' , '郑州市')
insert into tb values('004' , '002' , '老城区')
insert into tb values('005' , '003' , '二七区')
insert into tb values('006' , '003' , '金水区')
insert into tb values('007' , '003' , '惠济区')
insert into tb values('008' , '007' , 'A镇')
insert into tb values('009' , '007' , 'B镇')
insert into tb values('010' , '007' , 'C镇')

--查询指定节点及其所有子节点的函数 f_cid
create function f_cid(@ID varchar(3)) returns @t_level table(id varchar(3) , level int)
as
begin
  declare @level int
  set @level = 1
  insert into @t_level select @ID , @level
  while @@ROWCOUNT > 0
  begin
    set @level = @level + 1
    insert into @t_level select a.id , @level
    from tb a , @t_Level b
    where a.pid = b.id and b.level = @level - 1
  end
  return
end
go

select * from tb
--调用函数查询001(河南省)及其所有子节点
select a.* from tb a , f_cid('001') b where a.id = b.id order by a.id
--调用函数查询002(洛阳市)及其所有子节点
select a.* from tb a , f_cid('002') b where a.id = b.id order by a.id
--调用函数查询003(郑州市)及其所有子节点
select a.* from tb a , f_cid('003') b where a.id = b.id order by a.id

---------结果显示依次如下

id   pid  name
---- ---- ----------
001  NULL 河南省
002  001  洛阳市
003  001  郑州市
004  002  老城区
005  003  二七区
006  003  金水区
007  003  惠济区
008  007  A镇
009  007  B镇
010  007  C镇

(10 行受影响)

id   pid  name
---- ---- ----------
001  NULL 河南省
002  001  洛阳市
003  001  郑州市
004  002  老城区
005  003  二七区
006  003  金水区
007  003  惠济区
008  007  A镇
009  007  B镇
010  007  C镇

(10 行受影响)

id   pid  name
---- ---- ----------
002  001  洛阳市
004  002  老城区

(2 行受影响)

id   pid  name
---- ---- ----------
003  001  郑州市
005  003  二七区
006  003  金水区
007  003  惠济区
008  007  A镇
009  007  B镇
010  007  C镇

(7 行受影响)
 

 

--销毁
drop table tb
drop function f_cid