create function f_getSubs(@id int)
returns @re table(id int)
as
begin
insert into @re (id)
select id from tb where pid=@id
while exists (select * from tb where pid in (select id from @re) and id not in (select id from @re))
begin
insert into @re (id)
select * from tb where pid in (select id from @re) and id not in (select id from @re)
return
end
go
returns @re table(id int)
as
begin
insert into @re (id)
select id from tb where pid=@id
while exists (select * from tb where pid in (select id from @re) and id not in (select id from @re))
begin
insert into @re (id)
select * from tb where pid in (select id from @re) and id not in (select id from @re)
return
end
go
select a.* from tb a join f_getSubs(2) b on a.id=b.id
id pid name posno
1 0 一级分类 0001
2 0 一级分类 0002
3 1 二级分类 00010003
4 1 二级分类 00010004
5 1 二级分类 00010005
6 2 二级分类 00020006
7 2 二级分类 00020007
8 2 二级分类 00020008
9 8 三级分类 000200080009
10 8 三级分类 000200080010
至于posno的规律确定是用触发器做的处理，这样也不用每次为这个层次关系恼火，很多逐层统计问题也都可以解决。对于楼主所提到的问题我做了个如下处理
声明：这个纯粹是盗版zjcxc(邹建) 的做法，包括测试数据等
我拿过来大致改了一下^0^
,改过的算法不够优化，邹大哥可别怪罪:P.create table tb(id int,pid int,name varchar(10))
insert tb select 1, 0,'一级分类'
union all select 2, 0,'一级分类'
union all select 3, 1,'二级分类'
union all select 4, 1,'二级分类'
union all select 5, 1,'二级分类'
union all select 6, 2,'二级分类'
union all select 7, 2,'二级分类'
union all select 8, 2,'二级分类'
union all select 9, 8,'三级分类'
union all select 10,8,'三级分类'
go--创建处理函数
create function f_getChildId(@id int)
returns @re table(id int,pid int)
as
begin declare @pid int
declare @flag int
insert into @re (id,pid)
select id,pid from tb where pid=@id
set @flag=1
while @flag=1
begin
if exists(select * from tb where tb.pid in(select id from @re) and tb.id not in (select id from @re))
begin
insert into @re (id,pid)
select id,pid from tb where tb.pid in(select id from @re)
set @flag=1
end
else
set @flag=0
end
return
end
go
select id,pid from f_getparentid(2)
drop function f_getparentid
drop table tb