mysql - 按联接表数据排序

Question

这是我的表格:

主题

topic_id    name
1           "Help!"
2           "Hey!"
3           "What?"

帖子

post_id    topic    date        content
1          2        2016-05-01  "Hey there!"
2          1        2016-05-04  "How to use WIFI?"
3          1        2016-05-05  "I dont know"
4          1        2016-05-02  "What is WIFI?"
5          3        2016-05-06  "What what?"
6          2        2016-05-02  "Hello"

我有这个代码

SELECT * from topics
LEFT JOIN posts
ON posts.topic = topics.topic_id

我想加入只有最后(最近)记录的posts，并按posts.date对topics中的记录进行排序，但我不知道该怎么做。

预期结果:

topic_id    post_id   date          ...
3           5         2016-05-06    ...
1           3         2016-05-05    ...
2           6         2016-05-02    ...

Answer 1

请尝试这个查询

SELECT * from topics LEFT JOIN posts ON posts.topic = topics.topic_id
Left join (Select posts.topics, Max(posts.date) as Date From posts Group by posts.topics) as postgroup
on posts.date = postgroup.date and posts.topic = postgroup.topics;