请教一个sql怎样写
现在有A、B两个表，A是主表，B是子表,B的a_id关联A表的id
A表
id name
1 china
2 usa
B表
id a_id type
1 1 1
2 1 1
3 1 2
4 1 3
5 2 1
6 2 1
现在我想根据A表的name来分组，统计各type的数量
查询到的结果应该为
name type1_count type2_count type3_count
china 2 1 1
usa 2 0 0
请教各位大神，这个sql语句怎么写？ sql oracle 查询 分组 统计
[解决办法]
如果是固定的 下面这个可以 如果不固定 百度动态sql 行列转换吧 很多例子
with tbA as
(
select 1 id,'china' name from dual union all
select 2 id,'usa' name from dual
),tbB as
(
select 1 id,1 aid,1 type from dual union all
select 2 id,1 aid,1 type from dual union all
select 3 id,1 aid,2 type from dual union all
select 4 id,1 aid,3 type from dual union all
select 5 id,2 aid,1 type from dual union all
select 6 id,2 aid,1 type from dual
)
select a.name,
sum(decode(b.type,1,1,0)) type1_count,
sum(decode(b.type,2,1,0)) type2_count,
sum(decode(b.type,3,1,0)) type3_count
from tbA a,tbB b
where a.id = b.aid
group by a.name
name type1_count type2_count type3_count
-----------------------------------------
1china 2 1 1
2usa 2 0 0
