求一个sql语句!!!!!

Question

表tba是一个树结构的表
数据如下
id     name    fid
1      华南     0
2      华东     0
3      华北     0
4      华西     0
5      广东     1
6      广西     1fid记录的是该数据的父节点id我想获得父节点是0的所有数据,分别包含多少个子节点
如下
id     name    have
1      华南     2
2      华东     0
3      华北     0
4      华西     0
求性能高的sql语句,或存储过程!!!!!

Answer 1

http://topic.csdn.net/u/20100330/23/b2f663b1-0edf-4847-857e-e75640c90c1a.html

Answer 2

create table  #test(id int, name varchar(10), fid int)
insert #test select 1 ,'华南', 0
insert #test select 2 ,'华东', 0
insert #test select 3 ,'华北', 0
insert #test select 4 ,'华西', 0
insert #test select 5 ,'广东', 1
insert #test select 6 ,'广西', 1
with cte as(
select id as mid,*,0 as lev  from #test where fid=0
union all
select b.id ,a.*,lev+1  from #test a,cte b
where a.fid=b.id)select a.name,SUM(case when lev IS not null then 1 else 0 end) as num
from #test a left join cte b on  a.id=b.mid and b.lev<>0
where a.fid=0
group by a.namename       num
---------- -----------
华北         0
华东         0
华南         2
华西         0(4 行受影响)

Answer 3

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2010-04-01 20:17:06
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([id] int,[name] varchar(4),[fid] int)
insert [tb]
select 1,'华南',0 union all
select 2,'华东',0 union all
select 3,'华北',0 union all
select 4,'华西',0 union all
select 5,'广东',1 union all
select 6,'广西',1
--------------开始查询--------------------------
;with f1 as
(
 select * from tb where fid=0
 union all
 select a.* from tb a join f1 b on a.fid=b.id
),
f2 as
(
select id0=a.fid,b.fid,b.id from f1 a, tb b where a.fid=b.id
union all
select a.id0,b.fid,b.id from f2 a, tb b where a.fid=b.id
),
f3 as
(
select id0,cnt=count(1) from f2 group by id0
)
select a.*,数量=isnull(c.cnt,0) from f1 a left join f3 c on a.fid=c.id0
----------------结果----------------------------
/* id          name fid         数量
----------- ---- ----------- -----------
1           华南   0           0
2           华东   0           0
3           华北   0           0
4           华西   0           0
5           广东   1           2
6           广西   1           2(6 行受影响)
*/

Answer 4

with cte as(
select name as mname,*,0 as lev  from #test where fid=0
union all
select b.mname ,a.*,lev+1  from #test a,cte b
where a.fid=b.id)
select mname,SUM(case when lev=0 then 0 else 1 end) as num from cte
group by mnamemname      num
---------- -----------
华北         0
华东         0
华南         2
华西         0(4 行受影响)

Answer 5

--这样求出的是每一部门包含的子结点数
----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2010-04-01 20:17:06
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([id] int,[name] varchar(4),[fid] int)
insert [tb]
select 1,'华南',0 union all
select 2,'华东',0 union all
select 3,'华北',0 union all
select 4,'华西',0 union all
select 5,'广东',1 union all
select 6,'广西',1
--------------开始查询--------------------------
;with f1 as
(
 select * from tb where fid=0
 union all
 select a.* from tb a join f1 b on a.fid=b.id
),
f2 as
(
select id0=a.fid,b.fid,b.id from f1 a, tb b where a.fid=b.id
union all
select a.id0,b.fid,b.id from f2 a, tb b where a.fid=b.id
),
f3 as
(
select id0,cnt=count(1) from f2 group by id0
)
select a.*,数量=isnull(c.cnt,0) from f1 a left join f3 c on a.id=c.id0
----------------结果----------------------------
/* id          name fid         数量
----------- ---- ----------- -----------
1           华南   0           2
2           华东   0           0
3           华北   0           0
4           华西   0           0
5           广东   1           0
6           广西   1           0(6 行受影响)
*/

Answer 6

SELECT   a.id, a.name, ISNULL(b.fid_count, 0) AS have
FROM      tba AS a LEFT OUTER JOIN
                    (SELECT   fid, COUNT(*) AS fid_count
                     FROM      tba
                     GROUP BY fid) AS b ON a.id = b.fid