mysql - 获取与同一个表中的另一个属性匹配的所有行

Question

我有这个表(称为posts):

+---------+----------+---------+
| post_id | reply_to | user_id |
+---------+----------+---------+
|       1 |        0 |       1 |
|       2 |        0 |       1 |
|       3 |        2 |       2 |
|       4 |        1 |       3 |
|       5 |        3 |       2 |
+---------+----------+---------+

如何选择对 user_id = 1 帖子进行回复的所有帖子？

生成的表格应如下所示:

+---------+----------+---------+
| post_id | reply_to | user_id |
+---------+----------+---------+
|       3 |        2 |       2 |
|       4 |        1 |       3 |
+---------+----------+---------+

Answer 1

这是快速变体:

select *
from posts
where reply_to in (select post_id from posts where user_id = 1);

因此，内部查询选择用户的所有帖子，外部查询选择对这些帖子的所有直接回复