多表查询统计新人求帮助

[qq_18899733]

有表a 主键AID 名称Name
1 a1
2 a2
有表b 主键BID , 类别TypeID 日期 表a主键AID 表c主键CID
1 0s '2005-01-01' 1 1
2 1s '2006-01-01' 1 2
3 0s '2007-01-01' 2 3
4 0s '2007-11-11' 1 0

有表c 主键CID , 金额CMoney
1 5000
2 2000
3 3000

有表d 主键DID , 表b主键BID 金额Money
1 1 50
2 1 50
3 2 100
4 3 500

查询显示

表B类别 表B日期 表C金额 有表D金额 有表B(TypeID类别统计个数)
0s '2005' 5000 100 1
1s '2006' 2000 100 1
1s '2007' 3000 500 2

[--小F--]

select
    b.typeid,year(b.日期),c.CMoney,d.Money,count(b.typeid)
from
   a  inner join b on a.aid=b.aid 
       inner join c on b.cid=c.cid
       inner join d on c.did=d.did
group by
     b.typeid,year(b.日期),c.CMoney,d.Money

[--小F--]

select
    b.typeid,year(b.日期),c.CMoney,d.Money,count(b.typeid)
from
   a  inner join b on a.aid=b.aid 
       inner join
(select cid,sum(cmoney) as cmoney from  c group by cid) as c on b.cid=c.cid
       inner join 
(select did,sum(money) as money from d group by did) as d on c.did=d.did
group by
     b.typeid,year(b.日期),c.CMoney,d.Money