MySQL ORDER BY 然后分组

Question

我有这样的查询:

SELECT
    `Thread`.`ID` AS `ThreadID`,
    `Post`.`CreatorName` AS `LastPostBy`,
    `Post`.`CreatorUserID` AS `LastPostByID`,
    `Post`.`CreationDate` AS `LastPostDate`
FROM
    `Post`
        INNER JOIN
    `Thread` ON `Post`.`ThreadID` = `Thread`.`ID`
ORDER BY
    `Post`.`CreationDate` DESC

产生以下结果:

+---------+------------+--------------+--------------------+
|ThreadID | LastPostBy | LastPostByID | LastPostDate       |
+---------+------------+--------------+--------------------+
|       3 | User2      |           13 | 2016-03-02 09:38:44|
|       2 | User1      |            1 | 2016-03-01 09:27:24|
|       2 | User1      |            1 | 2016-02-14 21:27:06|
+---------+------------+--------------+--------------------+

现在我想让结果按 ThreadID 分组，所以我添加了 GROUP BY 子句，如下所示:

SELECT
    `Thread`.`ID` AS `ThreadID`,
    `Post`.`CreatorName` AS `LastPostBy`,
    `Post`.`CreatorUserID` AS `LastPostByID`,
    `Post`.`CreationDate` AS `LastPostDate`
FROM
    `Post`
        INNER JOIN
    `Thread` ON `Post`.`ThreadID` = `Thread`.`ID`
GROUP BY
    `Thread`.`ID`
ORDER BY
    `Post`.`CreationDate` DESC

我得到以下结果:

+---------+------------+--------------+--------------------+
|ThreadID | LastPostBy | LastPostByID | LastPostDate       |
+---------+------------+--------------+--------------------+
|       3 | User2      |           13 | 2016-03-02 09:38:44|
|       2 | User1      |            1 | 2016-02-14 21:27:06|
+---------+------------+--------------+--------------------+

但是，我的预期结果是记录与最新的 LastPostDate 分组，如下所示:

+---------+------------+--------------+--------------------+
|ThreadID | LastPostBy | LastPostByID | LastPostDate       |
+---------+------------+--------------+--------------------+
|       3 | User2      |           13 | 2016-03-02 09:38:44|
|       2 | User1      |            1 | 2016-03-01 09:27:24|
+---------+------------+--------------+--------------------+

如何得到这样的结果？

这个问题的名称是什么？这样以后就可以自己搜索了。

非常感谢。

编辑:

Yossi提供的解决方案和 Vipin Jain适用于第一个问题。

但是，当数据是这样的时候:

+---------+------------+--------------+--------------------+
|ThreadID | LastPostBy | LastPostByID | LastPostDate       |
+---------+------------+--------------+--------------------+
|       3 | User1      |            1 | 2016-03-02 18:55:19|
|       3 | User2      |           13 | 2016-03-02 09:38:44|
|       2 | User1      |            1 | 2016-03-01 09:27:24|
|       2 | User1      |            1 | 2016-02-14 21:27:06|
+---------+------------+--------------+--------------------+

提供的解决方案产生了这个结果:

+---------+------------+--------------+--------------------+
|ThreadID | LastPostBy | LastPostByID | LastPostDate       |
+---------+------------+--------------+--------------------+
|       3 | User2      |           13 | 2016-03-02 18:55:19|
|       2 | User1      |            1 | 2016-03-01 09:27:24|
+---------+------------+--------------+--------------------+

应该是这样的:

+---------+------------+--------------+--------------------+
|ThreadID | LastPostBy | LastPostByID | LastPostDate       |
+---------+------------+--------------+--------------------+
|       3 | User1      |            1 | 2016-03-02 18:55:19|
|       2 | User1      |            1 | 2016-03-01 09:27:24|
+---------+------------+--------------+--------------------+

有人能帮忙吗？

Answer 1

您缺少 MAX() 函数:

SELECT
    `Thread`.`ID` AS `ThreadID`,
    `Post`.`CreatorName` AS `LastPostBy`,
    `Post`.`CreatorUserID` AS `LastPostByID`,
    max(`Post`.`CreationDate`) AS `LastPostDate`
FROM
    `Post`
        INNER JOIN
    `Thread` ON `Post`.`ThreadID` = `Thread`.`ID`
GROUP BY
    `Thread`.`ID`
ORDER BY
    `Post`.`CreationDate` DESC

编辑:

SELECT
    `Thread`.`ID` AS `ThreadID`,
    `Post`.`CreatorName` AS `LastPostBy`,
    `Post`.`CreatorUserID` AS `LastPostByID`,
    `Post`.`CreationDate` AS `LastPostDate`
FROM
    `Post`
        INNER JOIN
    `Thread` ON `Post`.`ThreadID` = `Thread`.`ID`
WHERE (`Thread`.`ID`,`Post`.`CreationDate`) in(SELECT `Post`.`ThreadID`,
                                                      MAX(`Post`.`CreationDate`)
                                               FROM `Post`
                                               GROUP BY `Post`.`ThreadID`)
ORDER BY
    `Post`.`CreationDate` DESC