mysql - 如何查询存储在 MySQL 表之间的帖子？

Question

我将用户信息、用户发布的内容和 friend 都放在不同的表中。

用户:

id    user          email
1     userA         myemail@testa.com
2     userB         myemail@testb.com
3     userC         myemail@testc.com

内容

id    userid        date                    text
1     1             2015-09-01 00:00:00     content here
2     2             2015-09-02 00:00:00     more content here a
3     3             2015-09-03 00:00:00     more content here b
4     4             2015-09-04 00:00:00     more content here c

friend

id    userid1  userid2
1     1          2
2     2          3
3     2          4

我如何查询此数据以获取按日期排序的特定用户的所有 friend 的内容？

Answer 1

Select from user_content inner joined on friends，抓取任一用户的所有内容，其中 user_content 记录不属于当前用户，但当前用户是 friend 之一......或者只看查询

编辑:忘记添加最后 30 个帖子的限制

SELECT
     uc.user_id AS friend_user_id
    ,u.username AS friend_username
    ,u.email AS friend_email
    ,uc.date
    ,uc.text
FROM user_content uc
INNER JOIN friends f ON uc.user_id IN (f.user_id_1,f.user_id_2)
INNER JOIN user u ON uc.user_id = u.id
WHERE uc.user_id != ? AND ? IN (f.user_id_1, f.user_id2)
ORDER BY date DESC
LIMIT 30;