mysql - 如何使用比较规则连接表

Question

我有交易数据和主控价格，我想根据重量计算元素的价格，在表价格中我们只存储最小值weight 以避免规则之间的混淆(冲突规则 weight >0 and <5 | weight >3 and <5 for item with weight value 4)。我有 2 个这样的表

**Transactions table**

    id   item      weight
    --------------------
    1    Coffee    2
    2    Apple     7
    3    Computer  17

**Prices table**

    id  min_weight  price
    ----------------------
    1   0           200
    2   5           500
    3   10          1000
    4   20          2000

结果应该是

id   item      weight   price
------------------------------
1    Coffee    2        200  (because 2 >= 0 and < 5) (in table price))
2    Apple     7        500  (because 7 >= 5 and < 10)
3    Computer  17       1000 (because 17 >= 10 and < 20)

并且价格规则重量 >= 20 且价格为 2000 将被忽略。我尝试简单地加入那些表

SELECT * FROM transactions
LEFT JOIN prices ON prices.weight >= transactions.weight

但我不知道如何限制表价中的重量，我试图找出最小重量和最大重量(通过当前记录下方的下一个记录值)，但我不知道如何实现这一点。任何想法？谢谢。

编辑我只是尝试这个查询来找出记录的最大重量，我认为这个查询会忽略间隙 ID、重量顺序或价格，但在找到具有相同值的重量时仍然有点问题，但考虑另一种解决方案。

SELECT id, weight AS min_weight, 
(
    SELECT weight FROM prices AS next_prices
    WHERE weight = (SELECT MIN(weight) FROM prices AS all_prices WHERE weight > prices.weight)
) AS max_weight,
price 
FROM prices

Answer 1

此查询仅在重量和价格均按升序排列时有效。

select id,item,weight,max(price) as price
   from (
        select t1.id, 
               t1.item,
               t1.weight,
                p.price 
         from t_transaction t1, t_price p
        where  t1.weight >= p.min_weight
        ) tab
group by id,
         item,
         weight;