3表关联查询 并汇总金额

Question

我有3个表 CH_ReceiptMaster,CH_ReceiptDetail,CH_Department
在CH_ReceiptMaster 中有如下字段
ReceiptID ID，主键，自增
DepartmentID 相关部门ID在CH_ReceiptDetail 中有如下字段
DetailID ID，主键，自增
ReceiptID 主单据ID
PurchaseMoney 采购金额在CH_Department 中有如下字段
DepartmentID 自增主键ID
DepartmentName 部门名称我想求出每个部门的采购的汇总金额和部门
自己写的SQL语句 不能求和
select sum(i.PurchaseMoney) as PurchaseMoney ,i.DepartmentName from ch_receiptmaster m 
left join 
(select IsNull(SUM(d.PurchaseMoney), 0.00) as PurchaseMoney,m.ReceiptID,de.DepartmentName
FROM CH_ReceiptDetail d 
Inner JOIN CH_ReceiptMaster m 
ON d.ReceiptID = m.ReceiptID 
inner join ch_Department de
on  de.Departmentid =m.Departmentid 
where m.BusinessTime between '2011-02-01 00:00:00' and '2011-03-01 00:00:00' 
group by PurchaseMoney ,m.ReceiptID,de.DepartmentName)
 i on m.receiptid=i.receiptid
where m.BusinessTime between '2011-02-01 00:00:00' and '2011-03-01 00:00:00' 
group by PurchaseMoney,i.DepartmentName order by i.DepartmentName
请大侠指点指点

Answer 1

select 
IsNull(SUM(d.PurchaseMoney), 0.00) as PurchaseMoney,
de.DepartmentName
FROM CH_ReceiptDetail d 
Inner JOIN CH_ReceiptMaster m 
ON d.ReceiptID = m.ReceiptID 
inner join ch_Department de
on  de.Departmentid =m.Departmentid 
where m.BusinessTime between '2011-02-01 00:00:00' and '2011-03-01 00:00:00' 
group by de.DepartmentName

Answer 2

select c.DepartmentName,sum(isnull(a.PurchaseMoney,0)) as PurchaseMoney, 
from CH_ReceiptDetail a,CH_ReceiptMaster b,ch_Department c
where a.ReceiptID = b.ReceiptID 
     and a.Departmentid =c.Departmentid 
     and a.BusinessTime between '2011-02-01 00:00:00' and '2011-03-01 00:00:00' 
group by c.DepartmentName

Answer 3

select m.DepartmentID,DepartmentName,sum(PurchaseMoney) from CH_ReceiptDetail as dt
left join CH_ReceiptMaster as m on dt.ReceiptID=m.ReceiptID
left join CH_Department as d on d.DepartmentID=m.DepartmentID
group by m.DepartmentID,DepartmentName

Answer 4

select m.DepartmentID,DepartmentName,sum(PurchaseMoney) from CH_ReceiptDetail as dt
left join CH_ReceiptMaster as m on dt.ReceiptID=m.ReceiptID
left join CH_Department as d on d.DepartmentID=m.DepartmentID
where m.BusinessTime between '2011-02-01 00:00:00' and '2011-03-01 00:00:00' 
group by m.DepartmentID,DepartmentName