用另一个表中的MAX值联接表

问题描述：

select * from order
-------------------
|orderID|productID|
-------------------
|  1    |  234    |
|  2    |  234    |
|  3    |  123    |
-------------------

select * from product_supplier
-------------------------------------------
|ID|supplierID|productID|cost_price|latest|
-------------------------------------------
|1 |  1       | 234     | 1.00     | 0    |
|2 |  1       | 234     | 0.50     | 1    |
|3 |  2       | 123     | 0.75     | 1    |
-------------------------------------------


desired result
------------------------------
|orderID|productID|cost_price|
------------------------------
| 1     | 234     | 1.00     |
| 2     | 234     | 1.00     |
| 3     | 123     | 0.75     |
------------------------------

我希望将上面的两个表结合起来，以获得给定productID的orderID，productID和最大的cost_price.

I'm looking join the two tables above to get the orderID, productID and the largest cost_price for a given productID.

SELECT orderID, productID, cost_price 
FROM order LEFT JOIN product_supplier 
ON order.productID=product_supplier.productID AND MAX(cost_price);

给予ERROR 1111 (HY000): Invalid use of group function

如何将join表格限制为每个order一行，并与product_supplier中对应的最大cost_price值相连?

How can i restrict the joined table to one row per order, joined with the largest corresponding cost_price value in product_supplier?

答

解决此问题的规范方法是使用子查询从product_supplier表中识别产品及其最高价格，然后将该子查询加入到order获得所需的结果集.

The canonical way of approaching this is to use a subquery to identify the products and their maximum prices from the product_supplier table, and then to join this subquery to order to get the result set you want.

SELECT t1.orderID,
       t1.productID,
       COALESCE(t2.cost_price, 0.0) AS cost_price  -- missing products will appear
FROM order t1                                      -- with a zero price
LEFT JOIN
(
    SELECT productID, MAX(cost_price) AS cost_price
    FROM product_supplier
    GROUP BY productID
) t2
    ON t1.productID  = t2.productID AND
       t1.cost_price = t2.cost_price

用另一个表中的MAX值联接表

相关推荐