表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
... ...
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
... ...
where a1.fid=0 and a2.fid=a1.id
group by a1.thename,a2.thename
(select a.thename as dname,abc.thename from abc,(select * from abc) a where abc.fid=a.id) b
group by dname,thename
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
abc就是你的表名 表A,^_^
from abc,(select * from abc) a where abc.fid=a.id group by a.thename,abc.thename
对于这个呢,a是一个表别名 a为select * from abc的结果,当然,其实没必要这样的
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.thenameselect 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
----------- ---------- -----------
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 行)
另外, 楼主还应该说明是否统计中间级.比如, 下面这个统计出什么结果
ID thename FID
----------- ---------- -----------
1 aa 0
2 bb 1
3 bb 1
4 cc 1
5 dd 0
6 ee 4
7 ff 5
如何统计出:
bb 2
cc 1
ee 1
aa 4
ff 1
dd 1
......即大类跟在其小类的下边