我有一个费用表，其中包含基于项目定价的价格截止列表中的费用。例如，第一个收费范围是对于定价为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 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;
但这就产生了一个错误：
操作数应包含1列
最佳答案
这个怎么样？不附加，性能更好，更简单。
SELECT i.id, i.price, MAX(f.fee), i.price + MAX(f.fee)
FROM item i INNER JOIN fee f
ON i.price > f.cutoff
GROUP BY i.id, i.price
关于mysql - 如何联接或子查询代表层的表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/20587928/