MySQL LEFT JOIN，GROUP BY和ORDER BY无法按要求工作 [英] MySQL LEFT JOIN, GROUP BY and ORDER BY not working as required

问题描述

我有一张桌子

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

和一张桌子

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

我想执行类似的查询

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

您可能会猜到价格经常变化，因此我需要提供最新的价格.问题是我无法解决如何订购LEFT JOINed表.我尝试使用某些GROUP BY函数，例如MAX()，但这只会拉出列而不是行.

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.

谢谢.

推荐答案

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

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

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.

这篇关于MySQL LEFT JOIN，GROUP BY和ORDER BY无法按要求工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！