关于oracle多表分组统计

Question

商家表FACTORY
字段
id,name

订单表BOOKFORM
字段
id,status,facId(是FACTORY中的id)

status中会有 A,B,C,D,E 五种状态

现在需要将两表关联查询如下信息

FACTORY.id,FACTORY.name,BOOKFORM的数量，BOOKFORM中status的状态A的数量，。。。。B的数量

有什麽好的方法？

Answer 1

[code="sql"]
select FACTORY.id,
FACTORY.name,
count(BOOKFORM.id),
sum(case when BOOKFROM.status = 'A' then 1 else 0 end )
sum(case when BOOKFROM.status = 'B' then 1 else 0 end )
sum(case when BOOKFROM.status = 'C' then 1 else 0 end )
sum(case when BOOKFROM.status = 'D' then 1 else 0 end )
sum(case when BOOKFROM.status = 'E' then 1 else 0 end )
from FACTORY join BOOKFORM
on FACTORY.id = BOOKFORM.facId
group by FACTORY.id, FACTORY.name
[/code]