50分求查询语句，分少了没人理啊？

[zhangwei1437]

表 WLDA（物料资料） 字段 wlbh, wlmc, dj
1000 精肉 5.00
1001 味精 3.20
1002 糖 0.5
表 PF（配方表） 字段 cpbh wlbh yl
10000 1000 0.7
10000 1001 0.01
10000 1002 0.05
20000 1000 1.0
20000 3000 2.0
表 XFJLB(消费记录表) 字段 cpbh sl, jg cw lsh
10000 1 10 中厨 2006-1-1-0001
10000 2 10 中厨 2006-1-1-0001
20000 1 5 中厨 2006-1-1-0001
30000 1 6 面点 2006-1-1-0002
对XFJLB的销售的菜品进行利润分析
select
a.cpbh as 编号,
sum(a.sl) as 销售数量,
sum(a.sl*a.jg) as 销售金额,
sum(a.sl*c.dj*b.yl) as 物料成本,
sum(a.sl*a.jg)-sum(a.sl*c.dj*b.yl) as 利润
from XFJLB a inner join PF b on a.cpbh=b.cpbh
inner join WLDA c on b.wlbh=c.wlbh
group by a.cpbh
物料成本涉及到PF 和WLDA 根据XFJLB的cpbh查找PF，再根据PF中的wlbh找WLDA中的dj计算出成本，这个句子应该怎么写啊
因为xfjlb中不一定是一个菜点一次，还有 pf中是一个菜对应多种料啊，必须先算出来一个菜的成本，再乘销售数量才可以，可上面的句子怎么都不对啊，高手帮忙

[白发程序猿]

select x.cpbh as 编号,sum(x.sl) as 销售数量,sum(x.sl*x.jg) as 销售金额,sum(x.sl*t.zj) as 成本,sum(x.sl*x.jg)-sum(x.sl*t.zj) as 利润
from XFJLB x inner join (select cpbh,sum(yl*dj) as zj from PF p inner join WLDA w on p.wlbh=w.wlbh group by cpbh) t on x.cpbh=t.cpbh
group by x.cpbh

[zjcxc]

-- 分两次group
select
a.cpbh as 编号,
销售数量, 销售金额,
sum(销售数量*b.dj) as 物料成本,
sum(销售金额)-sum(销售数量*b.dj) as 利润
from(
select
a.cpbh,
b.wlbh,
sum(a.sl) as 销售数量,
sum(a.sl*a.jg) as 销售金额
from XFJLB a
inner join PF b on a.cpbh=b.cpbh
group by a.cpbh, b.wlbh
)a
inner join WLDA c on b.wlbh=c.wlbh
group by a.cpbh