从其他表中获取比MySQL join下一个较小的值

Question

我有两个表格orders

。

id  article amount
1   1       1               
2   2       50              

和价格

id  article min_amount  price
1   1       1           42.99   
2   2       1           5.06    
3   2       5           4.55    
4   2       10          4.3     
5   2       25          4.05    
6   2       100         2.66    

prices表格包含文章ID和最低购买数量，以获得批量折扣（这将改变订单的价格）。我希望将prices表格与orders表格连接起来，使结果看起来像：

id  article amount  price
1   1       1       42.99
2   2       50      4.05

订单编号2的金额已经超过最低限额25欧元，可以以4.05欧元的价格购买该文章，但金额仍然低于100欧元，无法享受更大的折扣。因此，查询应选择下一个较低的值。

到目前为止，我尝试了以下查询。

SELECT 
    orders.id AS id,
    orders.article,
    orders.amount,
    prices.price,
    (orders.amount - prices.min_amount) AS discount_diff
FROM orders
LEFT JOIN prices ON (prices.article = orders.article) AND (prices.min_amount <= orders.amount)

这是该结果

id  article amount  price   discount_diff
1   1       1       42.99   0
2   2       50      5.06    49
2   2       50      4.55    45
2   2       50      4.3     40
2   2       50      4.05    25 

您可以在"js"fiddle上找到此示例：http://sqlfiddle.com/#!9/1b2bf/8

这个链接是一个展示了关于it技术的代码实例。

- dersimn

Answer 1

你所需要的查询语句是：

这个

SELECT orders.id AS id,
       orders.article,
       orders.amount,
       prices.price
  FROM orders
         INNER JOIN prices ON ( prices.article = orders.article
                                and prices.min_amount <= orders.amount)
         INNER JOIN ( SELECT orders.article,
                             orders.amount,
                             min(prices.price) minprince
                        FROM orders
                               INNER JOIN prices ON (prices.article = orders.article
                                                   AND prices.min_amount <= orders.amount)
                       GROUP BY orders.article,
                                orders.amount) b
                ON (     prices.article = b.article
                    AND orders.amount = b.amount
                    AND prices.price = b.minprince) 

在这里查看：http://sqlfiddle.com/#!9/1b2bf/27

- Jorge Campos