>我有两个表,用户和帖子。我正在尝试编写一个查询来查找用户的最新帖子,但是遇到了麻烦。这是我到目前为止所拥有的。
select a.username, b.last_post from logins as a join (select login_id, entry as last_post from posts) as b where a.id = b.login_id
+-----------+---------------------+
| username | last_post |
+-----------+---------------------+
| something | 2013-10-08 22:12:00 |
| other | 2013-10-08 22:13:00 |
| test | 2013-10-08 22:13:03 |
| test | 2013-10-08 22:14:20 |
| hello | 2013-10-08 22:12:53 |
| hello | 2013-10-08 22:12:56 |
+-----------+----------+----------+
所以现在last_post
只是它正在拉取的帖子的时间戳。如何获得仅显示这些用户最后一篇文章的表格?
如果只需要两列,可以直接使用MAX()
SELECT a.username,
MAX(b.entry) last_post
FROM logins a
INNER JOIN posts b
ON a.id = b.login_id
GROUP BY a.username
否则,如果要显示所有表中的所有列,则可以使用子查询,该子查询分别获取每个login_id
的最新entry
SELECT a.*, b.*
FROM logins a
INNER JOIN posts b
ON a.id = b.login_id
INNER JOIN
(
SELECT login_id, MAX(entry) entry
FROM posts
GROUP BY login_id
) c ON b.login_id = c.login_id AND
b.entry = c.entry