求SQl语名，请指点！

[guanjueweimiao]

有如下四个表：
Mz_DeptDict(科室字典表)
ID RegDeptCode(主键),RegDeptName

Mz_Registe(挂号表)
RegCode(主键) RegDeptCode,... ...

Mz_RecipeBill(处方表)
RecipeCode(主键),RegCode,... ...

Mz_RecipeDetail(处方明细)
ID(主键) RecipeCode , RecipeMoney ,... ...

其中：Mz_Registe与Mz_RecipeBill是一对多关系，Mz_RecipeBill与Mz_RecipeDetail是一对多关系。
想得到的查询查果形式如下：
RegDeptCode(科室编号),RegDeptName(科室名称),RecipeMoney(金额)，人次(即挂号表中的记录数)
01 儿科 1000 10
02 外科 500 2
03 内科 800 6
... ... ... ...
请指点！

[pengdali]

select a.RegDeptCode,a.RegDeptName,sum(d.RecipeMoney),count(distinct b.RegCode) from Mz_DeptDict a
left join Mz_Registe b on a.RegDeptCode=b.RegDeptCode
left join Mz_RecipeBill c on b.RegCode=c.RegCode
left join Mz_RecipeDetail d on c.RecipeCode=d.RecipeCode
group by a.RegDeptCode,a.RegDeptName

[pengdali]

select a.RegDeptCode 科室编号,a.RegDeptName 科室名称,sum(d.RecipeMoney) 金额,count(distinct b.RegCode) 人次 from Mz_DeptDict a
left join Mz_Registe b on a.RegDeptCode=b.RegDeptCode
left join Mz_RecipeBill c on b.RegCode=c.RegCode
left join Mz_RecipeDetail d on c.RecipeCode=d.RecipeCode
group by a.RegDeptCode,a.RegDeptName

[zjcxc]

select a.RegDeptCode,a.RegDeptName
,RecipeMoney=sum(d.RecipeMoney),人次=count(distinct b.RegCode)
from Mz_DeptDict a
join Mz_Registe b on a.RegDeptCode=b.RegDeptCode
join Mz_RecipeBill c on b.RegCode=c.RegCode
join Mz_RecipeDetail d on c.RecipeCode=d.RecipeCode
group by a.RegDeptCode,a.RegDeptName

[poka]

试试这样，
select sum(d.RecipeMoney),count(distinct RegCode)
from Mz_DeptDict a left join
Mz_Registe b on a.RegDeptCode=b.RegDeptCode left join
Mz_RecipeBill c on b.RegCode=c.RegCode left join
Mz_RecipeDetail d on c.RecipeCode=d.RecipeCode
group by a.RegDeptCode,a.RegDeptName

[guanjueweimiao]

谢谢 大力!

[zjcxc]

回复了几分钟才回复上,就被大力抢了,晕