mysql - 如何连接或子查询表示层的表

Question

我有一个费用表，其中包含基于商品定价的价格截止列表中的费用。例如，第一个费用范围适用于价格为 0 美元的商品，下一层的费用为 1 美元。价格为 25 美元到下一层的商品将收取 2 美元的费用，依此类推。这是费用表:

费用

Cutoff Fee
------ ---
0      1
25     2
100    3

这是带有价格的商品表:

项目

Id     Price
------ ------
1      32
2      18
3      2
4      100

结果应该是这样的:

所需的项目费用结果

Id    Price   Fee     Total Price with fee
----- ------- ------- -----------
1     32      2       34
2     18      1       19
3     2       1       3
4     100     3       103

创建结果具有挑战性。下面是两个表之间连接结果的笛卡尔积:

Id  Price   Cutoff  Fee
--- ------- ------- ---
1   32      0       1 -- eliminate because price is above the next cut 25
2   18      0       1
3   2       0       1
4   100     0       1 -- eliminate because price is above the next cut 25
1   32      25      2
2   18      25      2 -- eliminate because price is below this cut
3   2       25      2 -- eliminate because price is below this cut
4   100     25      2 -- eliminate because price is above (equal to) the next cut 100
1   32      100     3 -- eliminate because price is below this cut
2   18      100     3 -- eliminate because price is below this cut
3   2       100     3 -- eliminate because price is below this cut
4   100     100     3

第一个where很简单:

where price >= cut

这将列表缩小到:

Id  Price   Cutoff  Fee
--- ------- ------- ---
1   32      0       1 -- eliminate because price is above the next cut 25
2   18      0       1
3   2       0       1
4   100     0       1 -- eliminate because price is above the next cut 25
1   32      25      2
4   100     25      2 -- eliminate because price is above (equal to) the next cut 100
4   100     100     3

问题来了:我如何过滤掉下一个定价层中的记录？这是我目前拥有的 sql。

select price, cutoff, fee from item, fee
where price >= cutoff;

我试过一个子查询:

select price, cutoff, fee, 
    (select min(cutoff), fee from fee where cutoff > price) as nextfee 
from item, fee
where price >= cutoff;

但这给出了错误:

Operand should contain 1 Column(s)

Answer 1

解决此问题的一种方法是使用相关子查询来获取 fee 表中截止值大于或等于价格的第一笔费用:

select id, price, fee, (price + fee) as totalprice
from (select id, price,
             (select fee
              from fee
              where price >= cutoff
              order by cutoff desc
              limit 1
             ) as fee
      from item
     ) i