--树形数据查询示例

--作者: 邹建

--示例数据

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