mysql - SQL : select items with number of comments AND latest comment date

Question

我想显示每个项目的评论数量和最后评论的日期。

SELECT item.*, 
  count(comments.itemid) AS commentcount, 
  comments.created AS commentcreated 
FROM table_items AS item 
LEFT JOIN table_comments AS comments ON (comments.itemid = item.id) 
WHERE item.published=1 
GROUP BY item.id 
ORDER BY item.created DESC

现在commentcreated 值是第一个评论的日期:我想要最后一个评论。 Order by 不适用于 LEFT JOIN。如何实现这一点？

Answer 1

尝试

SELECT item.*, 
  count(comments.itemid) AS commentcount, 
  max(comments.created) AS commentcreated 
FROM table_items AS item 
LEFT JOIN table_comments AS comments ON (comments.itemid = item.id) 
WHERE item.published=1 
GROUP BY item.id 
ORDER BY item.created DESC

您需要告诉 MySQL 组中的哪个日期要与 max()、min() 或其他聚合函数之一一起使用。