0

## 請教各位高手 SQL語法 求毛利及毛利率

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
``````

kevin0523 iT邦新手 5 級 ‧ 2020-07-16 16:46:45 檢舉

Zed_Yang iT邦新手 3 級 ‧ 2020-07-16 17:03:32 檢舉

(售價-進貨價) (amount-price) x (銷貨-銷貨退貨)(out-out_return) = 總利潤

kevin0523 iT邦新手 5 級 ‧ 2020-07-16 17:33:18 檢舉

### 1 個回答

1
REX
iT邦新手 5 級 ‧ 2020-07-16 23:45:08

``````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
``````
kevin0523 iT邦新手 5 級 ‧ 2020-07-17 03:01:16 檢舉