一个分组统计的问题，有没有高手来看看
表a:
type name size
0 a 2
0 b 3
0 a 1
0 c 3
表b:
type name size
0 a 2
0 b 3
0 a 1
0 c 3
现在要统计 按type和name 统计个数
既得到如下结果：
type name 表a的个数 表b的个数
0 a 2 2
0 b 1 1
0
[294 byte] By [msdn] at [2007-8-15 9:50:13] 
# 1 Re: 一个分组统计的问题，有没有高手来看看
select type,name,sum(numa) numa,sum(numb) numb from (select type,name,1 numa,0 numb from a union all select type,name,0 numa,1 numb from b) group by type,name
waterfirer at 2006-5-15 13:14:46 >
# 2 Re: 一个分组统计的问题，有没有高手来看看
select type,name,count(numa) numa,count(numb) numb
from (select * from a union all select * from b)
group by type,name
zhangjian01361 at 2006-5-15 14:32:25 >
# 3 Re: 一个分组统计的问题，有没有高手来看看
select a.type,a.name,a.acount,b.acount from
(select type, name, count(name) acount from a group by type,name) a,
(select type, name, count(name) bcount from b group by type,name) b
where a.type=b.type and a.name=b.name
order by a.name
SmallHand at 2006-5-15 16:32:20 >