mysql - 在范围表上计算成本

Question

我有两个类似于下面的表格:

ItemTable
-----------------------------
ItemId | NumberPurchased
1      | 10
2      | 90

我需要根据下表计算出订单的总成本，该表根据订购数量对每件商品的价格进行 float :

PriceBands
-----------------------------
ScaleId | LowerLimit | UpperLimit | CostPerItem
1       | 1          | 5          | 10
2       | 6          | 10         | 9
3       | 11         | 20         | 8
...

我需要以某种方式计算出总成本并将其加入第一个表(项目)。

有人可以帮忙吗？

为了清楚 ItemId 1 的计算如下:

NumberPurchased = 10
(5 * 10) + (5 * 9)  = 95

banding banding 仅对超过上限的项目产生影响。

Answer 1

如果您对每件商品的价格都相同，那么这样的事情可能会奏效:

SELECT i.itemID, i.NumberPurchased, i.NumberPurchased * p.costPerItem as "Cost"
FROM itemTable i, PriceBands p
WHERE i.NumberPurchased >= p.LowerLimit 
      AND i.NumberPurchased <= p.UpperLimit;

sqlfiddle demo

如果您想要不同的商品价格，您必须在 priceBands 中放置一个 itemId 并根据该 itemId 连接两个表。

---

要处理购买的每一层号码的增量成本，您可以:

SELECT i.itemID, i.NumberPurchased, SUM((
      CASE 
        WHEN i.NumberPurchased > p.upperLimit
          THEN p.upperLimit
        ELSE i.NumberPurchased - p.LowerLimit + 1
        END
      ) * p.costPerItem) AS "Cost"
FROM itemTable i, PriceBands p
WHERE i.NumberPurchased > p.LowerLimit
GROUP BY 1, 2

这将遍历 PriceBands 表中的每一行，同时 i.NumberPurchased > p.LowerLimit，同时求和:

• 将 p.costPerItem 乘以 p.upperLimit 而 i.NumberPurchased > p.upperLimit。 (5 * 10);
• 将 p.costPerItem 与 i.NumberPurchased - p.lowerLimit+ 1 相乘。(9 * 10-6 + 1 。这+ 1 将包括lowerLimit` 数)。

sqlfiddle demo