mysql - 从 3 个表和 "IN"中选择

Question

我的表:

feeds_time:

user_id | time
1       | 123123
2       | 1231111
3       | 1233223
...

用户关注

user_id | follow_id
1       | 2
1       | 3
2       | 3

还有表格帖子——但这在这个问题中并不重要。

实际上我的查询是这样的:

SELECT `id`, `name`, `user_id`, `time`, 'posts' as `what`
FROM `posts`
WHERE `user_id` IN (SELECT `follow_id`
                    FROM users_follow WHERE user_id = posts.user_id)

现在，我想添加一个 WHERE: WHERE posts.time > feeds_time.time by "current"user_id。

我该怎么做？

Answer 1

你可以试试这个

使用连接

SELECT A.`id`, A.`name`, A.`user_id`, A.`time`, A.'posts' as `what`
FROM `posts` A
JOIN feeds_time B ON A.user_id = B.user_id
WHERE A.`user_id` IN (SELECT `follow_id`
                    FROM users_follow WHERE user_id = posts.user_id)
AND A.time > B.time