在范围表上计算成本

如果你对每件商品都有相同的价格,那么这样的东西可能会奏效:

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);
• 乘 每项费用 带i.NumberPurchase-p.lowerLimit + 1. (9 * 10-6 + 1 . This + 1 is to include the lowerLimit `number也是)。

sqlfiddle demo

如果PriceBands表中有外键,则查询将返回您询问的内容:

select it.itemId, pb.costPerItem
from ItemTable it, 
     PriceBands pb
where it.itemId = pb.itemId
and it.numberPurchased >= pb.LowerLimit
and it.numberPurchased <= pb.upperLimit;