mysql - 从联接中获取最新记录

我有以下疑问：

SELECT f.name, MAX( cmt.created_at ) AS CommentDate, cmt.comment AS LastComment
FROM families f
LEFT JOIN comments cmt ON f.id = cmt.commentable_id
WHERE f.id =12212

我得到了评论的最后一个日期，但是我没有得到最后一条评论（根据那个日期），而是得到了第一条。怎么样？

最佳答案

试试看：

SELECT f.name, cmt.created_at AS CommentDate, cmt.comment AS LastComment
FROM families f
LEFT JOIN comments cmt ON f.id = cmt.commentable_id
WHERE f.id =12212
and cmt.created_at = (
                        SELECT MAX( cmt.created_at )
                        FROM families f
                        LEFT JOIN comments cmt ON f.id = cmt.commentable_id
                        WHERE f.id =12212
                      )

或

SELECT f.name, cmt.created_at AS CommentDate, cmt.comment AS LastComment
FROM families f
LEFT JOIN comments cmt ON f.id = cmt.commentable_id
WHERE f.id =12212
and cmt.created_at = (
                        SELECT MAX( created_at )
                        FROM comments
                        WHERE commentable_id =12212
                      )

关于mysql - 从联接中获取最新记录，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/15272205/