SQL:选择包含评论数量和最新评论日期的项目
问题描述:
我想显示包含评论数量和最后评论日期的项目,每个项目。SQL:选择包含评论数量和最新评论日期的项目
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值是第一个评论的日期:我想要最后一条评论。排序依据在LEFT JOIN上不起作用。这如何实现?
答
尝试
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()或其他聚合函数使用哪一个组中的日期。
是的,非常感谢!这工作!但是如果我想选择最新评论的标题呢? – Bert 2010-12-17 14:40:46
我认为这是一个不同的问题。 – Jaydee 2010-12-17 15:19:25