select
e.商品类型,
e.销量,
f.总销量,
g.盈亏平衡点,
(e.销量 * g.盈亏平衡点/f.总销量) as 各商品类型盈亏平衡点
from
(
select
商品类型,
sum(数量) as 销量
from
门店销售统计表
where 期间='2020-12-31'
group by 商品类型) e,
(select
sum(数量) as 总销量
from
门店销售统计表
where
期间='2020-12-31') f,
(select
(
d.固定成本/(
c.售价 - (a.变动成本/b.销量)
)
) as 盈亏平衡点
from
(
select
sum(金额) as 变动成本
from
门店变动成本汇总表
where
期间='2020-12-31'
) a,
(
select
sum(数量) as 销量
from
门店销售统计表
where
期间='2020-12-31'
) b,
(
select
sum(a.销售额)/sum(a.数量) as 售价,
sum(a.销售额) as 销售额
from
门店销售统计表 a
where
a.期间='2020-12-31'
) c,
(
select
sum(金额) as 固定成本
from
门店固定费用汇总表
where
日期='2020-12-31'
) d) g