问按平均购置率和销售率计算经营利润

EN

select
       final.profit,
       final.Balance_Stock_Quantity,
       final.Balance_Stock_Value
from
(
select tmp.date_,
       tmp.`Sale/Purchase`,
       tmp.product_id,
       tmp.quantity,
       tmp.rate,
       tmp.val,          
(
  case when tmp.`Sale/Purchase` = 'purchase' 
            then (@total_quant := @total_quant + tmp.quantity)
       else      (@total_quant := @total_quant - tmp.quantity)
  end
) as Balance_Stock_Quantity,
(
  case when tmp.`Sale/Purchase` = 'purchase' 
            then (@total_val := @total_val + tmp.val)
       else      (@total_val := @total_val - (@total_rate*tmp.quantity))
  end
) as Balance_Stock_Value,
(
  case when tmp.`Sale/Purchase` = 'purchase' 
            then (@total_rate := @total_val/@total_quant) 
       else @total_rate
  end
) as Balance_Stock_Rate,
(
  case when tmp.`Sale/Purchase` = 'sale' 
            then (@profit := (tmp.rate - @total_rate)*tmp.quantity) 
       else @profit
  end
) as profit

from
(
(select p_date as date_,
       'Purchase' as `Sale/Purchase`,
       p_product_id as product_id,
       p_quantity as quantity,
       p_rate as rate,
       (p_quantity * p_rate) as val
from purchase
where p_date BETWEEN '2017-11-23 00:00:00' AND '2017-11-23 05:00:00'
)
union all
(select s_date as date_,
       'Sale' as `Sale/Purchase`,
       s_product_id as product_id,
       s_quantity as quantity,
       s_rate as rate,
       (s_quantity * s_rate) as val
from sales
where s_date BETWEEN '2017-11-23 00:00:00' AND '2017-11-23 05:00:00' 
)
)tmp
cross join
(select 
        @total_quant := 0,
        @total_val := 0,
        @total_rate := 0, 
        @profit := 0) r
order by tmp.date_
)final
order by final.date_ desc
limit 1
;