mysql - 将 'ON' 子句中带有 ORDER BY 的子查询转换为 Join 进行优化

Question

我有这个查询:

SELECT prod.ProductID, prod.Name, prod.ProdExtID, ls.ProdServiceID
FROM Products prod 
LEFT JOIN ProductServices ls ON ls.ProdServiceID=(SELECT ProdServiceID FROM 
ProductServices WHERE ProductID=prod.ProductID ORDER BY Modified DESC LIMIT 
1) ;

此查询返回 175 行

我想将其转换为 JOIN。

我使用了以下查询:

SELECT prod.ProductID, prod.Name, prod.ProdExtID, ls1.ProductServicesID
FROM Products prod 
inner join ProductServices ls on ls.ProductID=prod.ProductID
inner JOIN (SELECT ProductServicesID, ProductID, max(Modified) as Modified
       FROM
      ProductServices group by Modified) as ls1 ON ls.ProductServicesID = 
  ls1.ProductServicesID and ls.Modified = ls1.Modified and ls.ProductID = 
  ls1.ProductID;

它不会返回正确的结果。我可以得到一些关于这方面的指导吗？目的是优化查询。使用 join 代替原始查询是个好主意吗？

谢谢!

Answer 1

试试这个:

SELECT ProductID
, Name
, ProdExtID
, ProdServiceID
FROM
(
SELECT prod.ProductID
, prod.Name
, prod.ProdExtID
, ls.ProdServiceID
, RANK() OVER(PARTITION BY ls.ProdServiceID ORDER BY ls.Modified DESC) AS rnk
FROM Products prod 
LEFT JOIN ProductServices ls ON ls.ProductID=prod.ProductID
) x
WHERE rnk = 1

所以基本上:您的加入需要在 ProductID 上，但您只需要最新的 ProdServiceID，对吧？