MySQL 选择两列之一匹配的位置并按另一个表中的列进行排序

Question

我有以下 mysql 语句，其中我为用户选择所有 friend 。

SELECT * 
FROM friends 
WHERE (user1 = FRIEND_ID_HERE AND confirmed = 1) 
   OR (user2 = FRIEND_ID_HERE AND confirmed = 1);

问题是 friend 可以是 friend 表中的user1或user2，所以我真的不知道如何加入members table 并在我不知道哪一列包含好友时按 last_online 对它们进行排序。

我怎样才能最有效地做到这一点？谢谢!

表成员

user_id | username | last_online
--------------------------------
      1 |    user1 | 1502296162
      2 |    user2 | 1502296161
      3 |    user3 | 1502296160
      4 |    user3 | 1502296159

同 table 好友

 id | user1 | user2 | confirmed
-------------------------------
  1 |     1 |     2 | 1502296162
  2 |     3 |     1 | 1502296161
  3 |     2 |     4 | 1502296160

Answer 1

我认为你需要的是这样的东西

SELECT * FROM members JOIN (
  SELECT user2 AS id FROM friends WHERE (user1 = FRIEND_ID_HERE AND confirmed = 1)
  UNION
  SELECT user1 AS id FROM friends WHERE (user2 = FRIEND_ID_HERE AND confirmed = 1)
) f ON memebers.id = f.id
ORDER BY last_online;