我有两个表,即供应商和产品
供应商:
id
charges_by(枚举"订单"、"产品"(
extra_fee_per_product
fee_per_order
产品:
成本
msrp
fee_per_product
vendor_id
我想看看所有利润率在100到50之间的产品
在我的项目中计算利润的公式是:
If(Vendors.charges_by=='Order') then
Vendor Fees = Products.Cost + Vendors.fee_per_order +
Vendors.extra_fee_per_product
ElSE IF(Vendors.charges_by=='Product') then Vendor Fees = Products.Cost +
Vendors.fee_per_product + Vendors.extra_fee_per_product
Gross Profit = Products.msrp - Vendor Fees
现在我们计算毛利润=
Margin = (Gross Profit / MSRP) * 100
我已经在列表中实现了搜索,我想在MySQL 的一个查询中看到这些记录
开始:
SELECT prod_id, (((msrp + 0.0) - (
CASE
WHEN v.charges_by = 'Order'
THEN p.cost + v.fee_per_order + v.extra_fee_per_product
WHEN v.charges_by = 'Product'
THEN p.cost + p.fee_per_product + v.extra_fee_per_product
END
)) / msrp) * 100 AS Margin
FROM Vendors v
INNER JOIN Products p
ON v.id = p.vendor_id
HAVING Margin BETWEEN 10 AND 100
工作摇杆:http://sqlfiddle.com/#!2/ddbaf2/8
这里的关键部分是msrp + 0.0
,它将计算视为浮点值而不是数字。
你在找什么?
SELECT (msrp - if(Vendors.charges_by=='Order',Products.Cost + Vendors.fee_per_order +
Vendors.extra_fee_per_product,Products.Cost +
Vendors.fee_per_product + Vendors.extra_fee_per_product)/ msrp )*100 as `Margin`
FROM Vendors
INNER JOIN Prodcuts ON Vendors.id = Prodcuts.vendor_id
select t1.cost, t1.msrp, t1.fee_per_product, t1.vendor_id from
((select (((p.msrp-(p.cost+v.fee_per_order+v.extra_fee_per_product))/p.msrp)*100) as margin
p.cost as cost, p.msrp as msrp, p.fee_per_product as fee_per_product, p.vendor_id as vendor_id
from products p inner join vendors v
on(v.id = p.vendor_id)
where v.charges_by in ['order', 'product']) as t1)
where t1.margin < 100 and t1.margin > 50;
试试这个。。。祝你好运!