MySQL LEFT JOIN、GROUP BY 和 ORDER BY 未按要求工作

MySQL LEFT JOIN, GROUP BY and ORDER BY not working as required

提问by Simon

I have a table

我有一张桌子

'products' => ('product_id', 'name', 'description') 

and a table

和一张桌子

'product_price' => ('product_price_id', 'product_id', 'price', 'date_updated')

I want to perform a query something like

我想执行类似的查询

SELECT `p`.*, `pp`.`price` 
FROM `products` `p` 
LEFT JOIN `product_price` `pp` ON `pp`.`product_id` = `p`.`product_id`
GROUP BY `p`.`product_id` 
ORDER BY `pp`.`date_updated` DESC

As you can probably guess the price changes often and I need to pull out the latest one. The trouble is I cannot work out how to order the LEFT JOINed table. I tried using some of the GROUP BY functions like MAX() but that would only pull out the column not the row.

你可能猜到价格经常变化，我需要拿出最新的。问题是我不知道如何订购 LEFT JOINed 表。我尝试使用一些 GROUP BY 函数，如 MAX() 但这只会拉出列而不是行。

Thanks.

谢谢。

回答by Simon

It appears that it is impossibleto use an ORDER BY on a GROUP BY summarisation. My fundamental logic is flawed. I will need to run the following subquery.

似乎不可能在 GROUP BY 摘要上使用 ORDER BY。我的基本逻辑有缺陷。我将需要运行以下子查询。

SELECT `p`.*, `pp`.`price` FROM `products` `p` 
LEFT JOIN (
    SELECT `price` FROM `product_price` ORDER BY `date_updated` DESC
) `pp` 
ON `p`.`product_id` = `pp`.`product_id`
GROUP BY `p`.`product_id`;

This will take a performance hit but as it is the same subquery for each row it shouldn't be too bad.

这会影响性能，但因为它是每一行的相同子查询，所以应该不会太糟糕。

回答by Matt Asbury

You need to set aliases properly I think and also set what you are joining on:

我认为您需要正确设置别名，并设置您要加入的内容：

SELECT p.*, pp.price 
FROM products AS p 
LEFT JOIN product_price AS pp
ON pp.product_id = p.product_id 
GROUP BY p.product_id 
ORDER BY pp.date_updated DESC

回答by nate c

This will give you the last updated price:

这将为您提供最后更新的价格：

select
  p.*, pp.price
from
  products p,
  -- left join this if products may not have an entry in prodcuts_price
  -- and you would like to see a null price with the product
  join 
  (
      select 
        product_price_id, 
        max(date_updated) 
      from products_price
      group by product_price_id
   ) as pp_max
    on p.product_id = pp.product_id
  join products_price pp on 
    pp_max.prodcuts_price_id = pp.products_price_id

回答by Hao

Mysqlism:

mysql主义：

SELECT p.*, MAX(pp.date_updated), pp.price 
FROM products p 
LEFT JOIN product_price pp ON pp.product_id = p.product_id
GROUP BY p.product_id 

Will work on some RDBMS:

将在一些 RDBMS 上工作：

SELECT p.*, pp.date_updated, pp.price 
FROM products p 
LEFT JOIN product_price pp ON pp.product_id = p.product_id
WHERE (p.product_id, pp.date_updated) 

   in (select product_id, max(date_updated) 
       from product_price 
       group by product_id)

Will work on most RDBMS:

将适用于大多数 RDBMS：

SELECT p.*, pp.date_updated, pp.price 
FROM products p 
LEFT JOIN product_price pp ON pp.product_id = p.product_id

WHERE EXISTS
(
    select null -- inspired by Linq-to-SQL style :-)
    from product_price 

    WHERE product_id = p.product_id 

    group by product_id

    HAVING max(date_updated) = pp.date_updated
)

Will work on all RDBMS:

将适用于所有 RDBMS：

SELECT p.*, pp.date_updated, pp.price 
FROM products p 
LEFT JOIN product_price pp ON pp.product_id = p.product_id

LEFT JOIN 
(
    select product_id, max(date_updated) as recent 
    from product_price 
    group by product_id
) AS latest 
ON latest.product_id = p.product_id AND latest.recent = pp.date_updated

And if nate c's code intent is to just get one row from product_price, no need to table-derive(i.e. join (select product_price_id, max(date_updated) from products_price) as pp_max), he might as well just simplify(i.e. no need to use the product_price_id surrogate primary key) it like the following:

如果nate c的代码意图只是从 product_price 获取一行，不需要表派生（即join (select product_price_id, max(date_updated) from products_price) as pp_max），他不妨简化（即不需要使用 product_price_id 代理主键），如下所示：

SELECT p.*, pp.date_updated, pp.price 
FROM products p 
LEFT JOIN product_price pp ON pp.product_id = p.product_id
WHERE pp.date_updated = (select max(date_updated) from product_price)