MySql:需要 SQL 查询来按某些规则对表中的记录集进行排序和分组

Question

在 MySql 中我有表:

idPost | idPostParent | postText                              | dateCreated         |
1      | 1            |  This is parent post no1              | 2012-01-01 12:00:00 |
2      | 2            |  This is parent post no2              | 2012-01-02 13:00:00 |
3      | 1            |  This is first reply on idPost:1      | 2012-01-02 13:30:00 | 
4      | 4            |  This is parent post no3              | 2012-01-04 10:00:00 |
5      | 2            |  This is first reply on idPost:2      | 2012-01-04 11:00:00 |
6      | 1            |  This is second reply on idPost:1     | 2012-01-05 15:00:00 |
7      | 2            |  This is second reply on idPost:2     | 2012-01-06 17:00:00 |

注意:

1. 如果 idPost = idPostParent，则检测到父帖子
2. 如果 idPost <> idPostParent 并且回复是与 idPostParent 中的帖子相关

我需要 SQL 查询来根据某些规则对表中的记录集进行排序和分组:

1. 帖子必须按 idPostParent 分组(先是父帖子，然后是它的帖子回复)
2. 最新输入的 PARENT 帖子或最近输入的回复必须在顶部。

结果集应该是:

idPost | idPostParent | postText                              | dateCreated         |
2      | 2            |  This is parent post no2              | 2012-01-02 13:00:00 |
5      | 2            |  This is first reply on idPost:2      | 2012-01-04 11:00:00 |
7      | 2            |  This is second reply on idPost:2     | 2012-01-06 17:00:00 |
1      | 1            |  This is parent post no1              | 2012-01-01 12:00:00 |
3      | 1            |  This is first reply on idPost:1      | 2012-01-02 13:30:00 |
6      | 1            |  This is second reply on idPost:1     | 2012-01-05 15:00:00 |
4      | 4            |  This is parent post no3              | 2012-01-04 10:00:00 |

原因:父帖子 idPost=2 有最新输入的回复帖子，因此它的线程必须在表格的顶部。

感谢任何可能为解决方案做出贡献的人!

Answer 1

这适用于您的测试数据

SELECT
posts.*
FROM posts
INNER JOIN 
  (
    SELECT idPostParent, MAX(dateCreated) as dateLastAnswer
    FROM posts
    GROUP by idPostParent
  ) AS pp ON posts.idPostParent=pp.idPostParent
ORDER BY pp.dateLastAnswer DESC, idPost ASC