MYSQL GROUP BY 未显示正确的日期

Question

我在 MYSQL v. 5.7.15-log 中的 GROUP BY 函数中遇到了一个小问题。

SELECT ca.CategoryID, ca.CategoryName, ca.CategoryDescription, co.Commenter, co.CommentDate 
FROM forum_category AS ca 
JOIN forum_comment AS co 
    ON co.CategoryID = ca.CategoryID
ORDER BY ca.CategoryID ASC, co.CommentDate DESC

结果

CategoryID  CategoryName    CategoryDescription Commenter   CommentDate
1           Admin           test1               Pino        "2017-04-16 15:47:41"
1           Admin           test1               Pino        "2017-04-16 15:36:40"
1           Admin           test1               Pino        "2017-04-16 15:25:30"
2           test            bla                 Pino        "2017-04-16 17:17:56"
2           test            bla                 Pino        "2017-04-16 17:17:21"
2           test            bla                 Pino        "2017-04-16 15:56:16"

我想要的地方是

CategoryID  CategoryName    CategoryDescription Commenter   CommentDate
1           Admin           test1               Pino        "2017-04-16 15:47:41"
2           test            bla                 Pino        "2017-04-16 17:17:56"

我尝试了一些不同的方法，但到目前为止还没有成功。

我确实在这里阅读并尝试

SELECT a.CategoryID, CategoryName, CategoryDescription, Commenter, CommentDate FROM (
    SELECT ca.CategoryID, ca.CategoryName, ca.CategoryDescription, co.Commenter, co.CommentDate 
    FROM forum_category AS ca 
    JOIN forum_comment AS co 
        ON co.CategoryID = ca.CategoryID
    ORDER BY ca.CategoryID ASC, co.CommentDate DESC
) AS a
ORDER BY a.CategoryID

但这也只会导致日期错误

CategoryID  CategoryName    CategoryDescription Commenter   CommentDate
1           Admin           test1               Pino        "2017-04-16 15:25:30"
2           test            bla                 Pino        "2017-04-16 15:56:16"

提前感谢 MYSQL Ninjas

Answer 1

试试这个:

SELECT ca.CategoryID, ca.CategoryName, ca.CategoryDescription, co.Commenter, MAX(co.CommentDate) as max_date
FROM forum_category AS ca 
JOIN forum_comment AS co ON co.CategoryID = ca.CategoryID
GROUP BY ca.CategoryID, ca.CategoryName, ca.CategoryDescription, co.Commenter
ORDER BY ca.CategoryID ASC, max_date DESC;