--树形数据查询示例

--参考

--树形数据查询示例
--作者: 邹建

--示例数据
create table [tb]([id] int identity(1,1),[pid] int,name varchar(20))
insert [tb] select 0,'中国'
union  all  select 0,'美国'
union  all  select 0,'加拿大'
union  all  select 1,'北京'
union  all  select 1,'上海'
union  all  select 1,'江苏'
union  all  select 6,'苏州'
union  all  select 7,'常熟'
union  all  select 6,'南京'
union  all  select 6,'无锡'
union  all  select 2,'纽约'
union  all  select 2,'旧金山'
go

--查询指定id的所有子
create function f_cid(
@id int
)returns @re table([id] int,[level] int)
as
begin
    declare @l int
    set @l=0
    insert @re select @id,@l
    while @@rowcount>0
    begin
        set @l=@l+1
        insert @re select a.[id],@l
        from [tb] a,@re b
        where a.[pid]=b.[id] and b.[level]=@l-1
    end
/*--如果只显示最明细的子(下面没有子),则加上这个删除
    delete a from @re a
    where exists(
        select 1 from [tb] where [pid]=a.[id])
--*/
    return
end
go

--调用(查询所有的子)
select a.*,层次=b.[level] from [tb] a,f_cid(2)b where a.[id]=b.[id]
go

--删除测试
drop table [tb]
drop function f_cid
go