parentID为父节点 child为子节点 ，Num为子节点的数量，子节点也可能为父节点、
编写一个存储过程，输出一个父节点的所有最终节点和数量，
parentID child Num
010001 010002 11
010001 010003 33
010002 020001 44
010002 020003 55
010003 NULL
020001 NULL
020003 NULL
---------------------------------------------------------------
改一下結構吧如
id,parentID,title
數據如：
1，0，AA
2，0,BB
3,1,CC
4,3,DD
5,4,EE
6,4,FF
7,3,GG
就是樹：
AA BB
¦
CC
¦--- ¦
DD GG
¦---- ¦
EE FF
---------------------------------------------------------------
参考
http://expert.csdn.net/Expert/topic/1230/1230187.xml?temp=.8161737
上面我的解决方法，呵呵。
---------------------------------------------------------------
drop table new_tree
go
/parent对应父亲结点，child对应儿子结点，如果child is NULL则本结点为叶子结点/
create table new_tree ( parent varchar(80), child varchar(80),weight int)
go
insert new_tree values ( '1','2',3);
insert new_tree values ( '1','3',4);
insert new_tree values ( '2','4',3);
insert new_tree values ( '2','5',5);
insert new_tree values ( '3','6',7);
insert new_tree values ( '3','7',3);
insert new_tree values ( '3','8',2);
insert new_tree values ( '6','9',1);
insert new_tree values ( '5','10',4);
insert new_tree values ( '4','11',3);
insert new_tree values ( '9','12',7);
insert new_tree values ( '7',NULL,1);
insert new_tree values ( '8',NULL,4);
insert new_tree values ( '10',NULL,3);
insert new_tree values ( '11',NULL,3);
insert new_tree values ( '12',NULL,7);
drop proc proc_new_tree
go
/@parent 输入根结点标识，@mode为0 则输出为所有子孙记录，否则输出所有叶子结点/
create proc proc_new_tree (@parent varchar(80),@mode int =0)
as
begin
set nocount on
/如果不是SQLSERVER2000可以用临时表/
declare @tmp1 table ( parent varchar(80), child varchar(80),weight int)
declare @tmp2 table ( parent varchar(80), child varchar(80),weight int)
declare @tmp3 table ( parent varchar(80), child varchar(80),weight int)
insert @tmp1 select parent,child,weight from new_tree where parent = @parent
insert @tmp3 select parent,child,weight from new_tree where parent = @parent
/循环的次数等于树的深度/
while exists(select * from @tmp1 where child is not NULL)
begin
insert @tmp2 select a.parent,a.child , b.weighta.weight from new_tree a,@tmp1 b where a.parent = b.child
/@tmp2表中存本次查询的层次的所有结点*/
delete from @tmp1 where child is not NULL
/@tmp1表中最终存的是叶子结点/
insert @tmp1 select * from @tmp2
/@tmp3表中最保存每次查到的子孙/
insert @tmp3 select * from @tmp2
delete from @tmp2
end
if @mode =0 select * from @tmp3
else select * from @tmp1
set nocount off
end
go
proc_new_tree '1',1
go