select a.配方ID ,sum(b.烟叶数量 * case when isnull(d.单价,0) = 0 then c.不变价 else d.单价 end)/sum(b.烟叶数量) as 配方单所对应的价格 from 配方定义表 a left join 配方定义表明细 b on a.配方ID = b.配方ID left join 烟叶定义表 c on b.烟叶ID = c.烟叶ID left join 烟叶单价表 d on b.烟叶ID = d.烟叶ID and datediff(month,启用时间,a.起始日期) = 0 and 启用时间 = (select max(启用时间) from 烟叶单价表 where 烟叶ID = b.烟叶ID and datediff(month,启用时间,a.起始日期) = 0)group by a.配方ID
,sum(b.烟叶数量 * case when isnull(d.单价,0) = 0 then c.不变价 else d.单价 end)/sum(b.烟叶数量) as 配方单所对应的价格
from 配方定义表 a
left join 配方定义表明细 b on a.配方ID = b.配方ID
left join 烟叶定义表 c on b.烟叶ID = c.烟叶ID
left join 烟叶单价表 d on b.烟叶ID = d.烟叶ID
and datediff(month,启用时间,a.起始日期) = 0
and 启用时间 = (select max(启用时间)
from 烟叶单价表
where 烟叶ID = b.烟叶ID
and datediff(month,启用时间,a.起始日期) = 0)group by a.配方ID