我已经为朋友设置了用户在线状态
SELECT * FROM users
WHERE
(id_user IN
(SELECT CASE
WHEN friend_one = '{$id}'
THEN friend_two
WHEN friend_two = '{$id}'
THEN friend_one
END
FROM friends
WHERE status = '1')
) AND (status = '1' OR ($date - last_login) <= 300)
而且它的工作原理是，该mysql仅显示好友登录状态列表。
但是问题是当我尝试设置其他用户的登录状态时，该状态仅显示已登录的其他人的列表，但未显示在我们的朋友列表中。
SELECT * FROM users
WHERE
(id_user NOT IN
(SELECT CASE
WHEN friend_one = '{$id}'
THEN friend_two
WHEN friend_two = '{$id}'
THEN friend_one
END
FROM friends
WHERE status = '1')
) AND (status = '1' OR ($date - last_login) <= 300)
所以这就是我想要的结果
Logged as: khrisna
Friend list: Ben, Alpha, Ralph
All User: Ben, Alpha, John, Mark, Zayn
Online friend list: Ben, Alpha
Another online user: John, Mark, Zayn
有人可以帮助我达到我想要的结果吗？
更新
users表friends表我想为结果做这样的事情
在线用户：Khrisna Gunanasurya //登录帐户
在线朋友列表：Alpha，John //登录帐户的在线朋友
在线其他用户：Mark，Ralph //其他登录用户而不是登录帐户的朋友
最佳答案
一种方法是：
获取所有在线好友
SELECT *
FROM
(
SELECT CASE WHEN friend_one = ? THEN friend_two ELSE friend_one END friend
FROM friends
WHERE ? IN(friend_one, friend_two)
AND status = 1
) f JOIN users u
ON f.friend = u.id_user
WHERE status = 1 OR last_login >= UNIX_TIMESTAMP() - 300;
获取所有在线的非朋友
SELECT *
FROM users u
WHERE id_user <> ?
AND NOT EXISTS
(
SELECT *
FROM friends
WHERE ? IN(friend_one, friend_two)
AND status = 1
AND u.id_user = CASE WHEN friend_one = ? THEN friend_two ELSE friend_one END
)
AND (status = 1 OR last_login >= UNIX_TIMESTAMP() - 300);
问号代表已登录用户ID的值（例如Khrisna Gunanasurya）
这是SQLFiddle演示
您的php代码可能看起来像
$sql = "SELECT *
FROM
(
SELECT CASE WHEN friend_one = ? THEN friend_two ELSE friend_one END friend
FROM friends
WHERE ? IN(friend_one, friend_two)
AND status = 1
) f JOIN users u
ON f.friend = u.id_user
WHERE status = 1 OR last_login >= UNIX_TIMESTAMP() - 300";
$stmt = $pdo->prepare($sql);
$stmt->execute(array_fill(0, 2, $id));
关于mysql - MySQL:用户在线状态[HELP]，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/26293060/