大类小类分组统计？

[baisun]

表A
ID thename FID
1 aa 0
2 bb 1
3 bb 1
4 cc 1
5 dd 0
6 ee 1
7 ff 5
... ...
如何统计出：

aa bb 2
aa cc 1
aa ee 1
dd ff 1
... ...

[tx1icenhe]

如果只有两级

select a1.thename as pname,a2.thename,count(*) as cnt from a a1,a a2
where a1.fid=0 and a2.fid=a1.id
group by a1.thename,a2.thename

[csdnweii]

select max(dname) as dname,max(thename) as thename,count(*) as coun from
(select a.thename as dname,abc.thename from abc,(select * from abc) a where abc.fid=a.id) b
group by dname,thename

[csdnweii]

这个也可以
select a.thename as dname,abc.thename,count(*) as coun
from abc,(select * from abc) a where abc.fid=a.id group by a.thename,abc.thename

[baisun]

abc你哪里来的？

[itblog]

ABC当然是你的表名了!

[xyxfly]

abc你哪里来的？
abc就是你的表名 表A，^_^

[baisun]

那a是什么？我表名是a

[xyxfly]

那a是什么？我表名是a

select a.thename as dname,abc.thename,count(*) as coun
from abc,(select * from abc) a where abc.fid=a.id group by a.thename,abc.thename
对于这个呢，a是一个表别名 a为select * from abc的结果，当然，其实没必要这样的

[xyxfly]

上面的结果：
create table a
(
ID int,
thename varchar(10),
FID int
)
insert a
select 1, 'aa', 0 union all
select 2, 'bb', 1 union all
select 3, 'bb', 1 union all
select 4, 'cc', 1 union all
select 5, 'dd', 0 union all
select 6, 'ee', 1 union all
select 7, 'ff', 5
select * from a
select a1.thename as pname,a2.thename,count(*) as cnt from a a1,a a2
where a1.fid=0 and a2.fid=a1.id
group by a1.thename,a2.thename

select max(dname) as dname,max(thename) as thename,count(*) as coun from
(select a1.thename as dname,a.thename from a, a a1 where a.fid=a1.id) b
group by dname,thename

[xyxfly]

所影响的行数为 7 行）

ID thename FID
----------- ---------- -----------
1 aa 0
2 bb 1
3 bb 1
4 cc 1
5 dd 0
6 ee 1
7 ff 5

（所影响的行数为 7 行）

pname thename cnt
---------- ---------- -----------
aa bb 2
aa cc 1
aa ee 1
dd ff 1

（所影响的行数为 4 行）

dname thename coun
---------- ---------- -----------
aa bb 2
aa cc 1
aa ee 1
dd ff 1

（所影响的行数为 4 行）

[free_pop2k]

有点像一个BOM表。不知道有多少级

[zjcxc]

无限级的话, 要用循环了.
另外, 楼主还应该说明是否统计中间级.

比如, 下面这个统计出什么结果
ID thename FID
----------- ---------- -----------
1 aa 0
2 bb 1
3 bb 1
4 cc 1
5 dd 0
6 ee 4
7 ff 5

[baisun]

两极，呵呵；
如何统计出：
bb 2
cc 1
ee 1
aa 4
ff 1
dd 1
......即大类跟在其小类的下边