想請教下各位高手 MySql 問題
欄位stock_type:
in: 進貨
in_return: 進貨後退貨 (計算時total欄位要轉為負值）
out: 銷貨
out_return: 銷貨後退貨(計算時total欄位要轉為負值)
select
sum( IF(stock_type = 'out_return', -1 * amount, amount)) as out_amount_total,
sum( IF(stock_type = 'out_return', -1 * total, total)) as out_price_total
from stock_items_copy
where stock_type in ('out', 'out_return')
group by product_id
order by out_price_total desc
目前這樣的語法可以求出產品的
銷售總數量 out_amount_total
銷售總額 out_price_total
想請問各位高手
要如何在同個SQL語法中 使用in及in_return欄位 求得 毛利 和 毛利率呢?
毛利 = 銷貨收入 - 進貨成本
毛利率 = （銷貨收入－進貨成本）／銷貨收入 x 100%
銷貨收入: out_price_total
進貨成本: (in_price_total / in_amount_total) * out_amount_total
隨文附上測試的資料
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1c79f5168bebbac3cf256829250b856d
感謝!!
已邀請的邦友 {{ invite_list.length }}/5
加上CTE的語法，湊出進出貨金額的欄位SQL累贅了點
主要要看計算式有沒有寫對
參考看看
with cte AS (select si.product_id,
sum(IF(stock_type='in',amount,-1*amount)) as in_price_amount ,
sum(IF(stock_type='in',total,-1*total)) as in_price_total ,
b.out_amount_total,
b.out_price_total
from stock_items as si
inner join
(select
product_id,
sum( IF(stock_type = 'out_return', -1 * amount, amount)) as out_amount_total,
sum( IF(stock_type = 'out_return', -1 * total, total)) as out_price_total
from stock_items
where stock_type in ('out', 'out_return')
group by product_id ) b
on si.product_id=b.product_id
where stock_type in ('in','in_return')
group by si.product_id
)
select
c.product_id,
convert(c.out_price_total-((c.in_price_total/c.in_price_amount)*c.out_amount_total),decimal(16,2)) as '毛利',
convert((c.out_price_total-((c.in_price_total/c.in_price_amount)*c.out_amount_total))/c.out_price_total*100,decimal(16,2)) as '毛利率%'
from cte c
iThome鐵人賽
看影片追技術