查询优化

Question

我需要最新的4个不同类别的4个产品。此查询不起作用

SELECT 
  ProductID,ProductName,thumb
  FROM 
  tbproduct 
  WHERE 
  status =2 
GROUP BY 
`CATEGORYID` 
ORDER BY 
`ProductID` DESC 
LIMIT 4

因此，我首先使用以下查询按所有records.and排序...

SELECT 
ProductID, ProductName, thumb 
FROM 
  (
  SELECT 
  ProductID,ProductName,thumb,CATEGORYID 
  FROM 
  tbproduct 
  WHERE 
  status =2 
  ORDER BY 
  `ProductID` DESC
  ) AS tmp 
GROUP BY 
`CATEGORYID` 
ORDER BY 
`ProductID` DESC 
LIMIT 4

这个查询很慢，有人能给我提一下它的快速查询吗？我用的是MySQL。

Answer 1

如果你只是简单地使用：

SELECT 
  ProductID,ProductName,thumb,CATEGORYID 
  FROM tbproduct 
  WHERE status=2
  GROUP BY CATEGORYID
  ORDER BY ProductID DESC
  LIMIT 4

Answer 2

 SELECT 
  Max(ProductID), ProductName,thumb
  FROM 
  tbproduct 
  WHERE 
  status =2 
GROUP BY 
`CATEGORYID` 
ORDER BY 
 ProductID DESC 
LIMIT 4

这不管用吗？

不确定这是否有帮助，请参阅更新后的查询。请参阅Group-by Aggragate的用法。

注意:这不会可靠地返回其他列。请在此处查找left-self-exclusion join

Answer 3

SELECT 
ProductID, ProductName, thumb 
FROM tbproduct 
WHERE ProductID in (
  SELECT max(ProductID)
  FROM tbproduct
  WHERE status = 2
  GROUP BY CATEGORYID
  ORDER BY ProductID DESC
  LIMIT 4
) ORDER BY ProductID DESC 

这应该可以了。