mysql - 如何更改 where 条件以从其他数据库表中读取数字？

Question

我想用 users_tb 中的 id 更改数字 2。我该如何添加，users_tb 与其他表没有关系，它只是一个列 ID，每次在某些事件中都会更新

SELECT messages.message,messages.sent_date, receiver_user.username, sender_user.username  FROM messages
        INNER JOIN users AS sender_user ON messages.msg_fk_user_id = sender_user.id
        INNER JOIN users AS receiver_user ON messages.receiver_id = receiver_user.id
        WHERE msg_fk_user_id = 1 AND receiver_id = 2 OR msg_fk_user_id = 2 AND receiver_id = 1
        ORDER BY `sent_date`;

Answer 1

尝试使用CROSS JOIN

SELECT messages.message,
       messages.sent_date, 
       receiver_user.username, 
       sender_user.username  
FROM messages
INNER JOIN users AS sender_user ON messages.msg_fk_user_id = sender_user.id
INNER JOIN users AS receiver_user ON messages.receiver_id = receiver_user.id
CROSS JOIN users_tb
WHERE msg_fk_user_id = 1 
AND receiver_id = users_tb.id
OR msg_fk_user_id = users_tb.id 
AND receiver_id = 1
ORDER BY `sent_date`

The cross join returns a Cartesian product of rows from both tables. Unlike the INNER JOIN or LEFT JOIN , the cross join does not establish a relationship between the joined tables. ... In general, if the first table has n rows and the second table has m rows, the cross join will result in n x m rows.

我不知道数据库的结构，但如果你想使用上面的查询，我认为你需要在 users_tb 表上插入一些过滤器。或者让我知道您是如何得到数字 2 的，我会尝试通过改进我的查询来帮助您，例如:

...
CROSS JOIN (SELECT * FROM users_tb ORDER BY id DESC LIMIT 1) as users_tb
...