mysql - 从 MySQL 数据库中选择 friend ，然后选择他们的详细信息(可能需要加入)

Question

我有一个 MySQL 表:

Friends
--------
fr_id
user_id_a
user_id_b
approval_status
---------

其背后的基本逻辑是，如果用户 #1 向 friend #2 发送好友请求，表条目将如下所示:

------------------------------------------------
fr_id - user_id_a - user_id_b - approval_status
 1         1           2             no
------------------------------------------------

现在只有当用户批准请求时，他们才会成为 friend 。

要选择对用户 #1 的所有请求，我可以使用以下查询:

SELECT * FROM friendship 
WHERE 
approved_status='no'
AND
(user_id_a = 1 OR user_id_b = 1)    

结果将如下所示:

fr_id - user_id_a - user_id_b - approval_status
  1         1           2             no

如何区分当前用户是具有 user_id_a 还是 user_id_b 的用户，以便我可以使用另一个 ID 从另一个表中提取他们的信息？

@aweis 提供的答案

SELECT * , 
CASE WHEN user_id_a =3
THEN  'a'
ELSE  'b'
END AS UserIdColumn, 
CASE WHEN user_id_a =3
THEN user_id_b
ELSE user_id_a
END AS NotCurrentUserID
FROM fr_friendship
WHERE approved_status =  'yes'
AND (
user_id_a =3
OR user_id_b =3
)

我可以选择 friend ，但问题的第二部分是加入，然后与另一个表一起获取 friend 的用户名...我尝试了这个，但它给了我错误..

#1054 - Unknown column 'NotCurrentUserID' in 'on clause'



SELECT f.*, p.username AS friend,

CASE WHEN user_id_a = 1 THEN 'a'
ELSE 'b'
END AS UserIdColumn,

CASE WHEN f.user_id_a = 1 THEN f.user_id_b
ELSE f.user_id_a
END AS NotCurrentUserID

FROM fr_friendship AS f
LEFT JOIN u_profile AS p ON p.user_id_login = NotCurrentUserID

WHERE 
f.approved_status = 'yes'
AND
(f.user_id_a = 1 OR f.user_id_b = 1) 

编辑:解决方案

我成功解决了这个问题......我希望它对某人有帮助:

SELECT a.friend_id, u.username
FROM
      ( SELECT CASE WHEN user_id_a = 1
                      THEN user_id_b 
                      ELSE user_id_a 
               END AS friend_id 
        FROM fr_friendship
        WHERE approved_status = 'yes' AND (user_id_a = 1 OR user_id_b = 1)
      ) AS a

LEFT JOIN u_profile AS u ON u.user_id_login = a.friend_id

Answer 1

您可以使用case来确定当前用户位于哪一列!在下面的代码中，我显示了当前用户所在的列，并且还有第二列包含非当前用户的所有 id:

SELECT
*,
case when user_id_a = 1 then 'a'
else 'b'
end as UserIdColumn,
case when user_id_a = 1 then user_id_b
else user_id_a
end as NotCurrentUserID
FROM friendship 
WHERE 
approved_status = 'no'
AND
(user_id_a = 1 OR user_id_b = 1) 

左连接与内连接

考虑下面的数据结构和数据。在此示例中，用户 ID 1 链接到用户 ID 2、3 和 4，但只有用户 ID 2 和 3 有个人资料。

create table fr_friendship
(
  fr_id int,
  user_id_a int,
  user_id_b int,
  approved_status varchar(3)
);

create table u_profile
(
  user_id_login int,
  username varchar(100)
);

insert into fr_friendship values (1, 1, 2, 'yes');
insert into fr_friendship values (2, 3, 1, 'yes');
insert into fr_friendship values (3, 1, 4, 'yes');

insert into u_profile values (2,'john doe');
insert into u_profile values (3, 'jane doe');

左连接

使用左连接时:

SELECT a.friend_id, u.username
FROM
      ( SELECT CASE WHEN user_id_a = 1
                      THEN user_id_b 
                      ELSE user_id_a 
               END AS friend_id 
        FROM fr_friendship
        WHERE approved_status = 'yes' AND (user_id_a = 1 OR user_id_b = 1)
      ) AS a

LEFT JOIN u_profile AS u ON u.user_id_login = a.friend_id

来自“内部选择”的所有匹配都在结果集中(连接的左侧部分)，并且匹配的配置文件被连接，但如果用户没有配置文件，则右侧部分包含纯空。上面选择的结果是:

FRIEND_ID  USERNAME
2          john doe
3          jane doe
4          NULL

内连接

使用内连接时:

SELECT a.friend_id, u.username
FROM
      ( SELECT CASE WHEN user_id_a = 1
                      THEN user_id_b 
                      ELSE user_id_a 
               END AS friend_id 
        FROM fr_friendship
        WHERE approved_status = 'yes' AND (user_id_a = 1 OR user_id_b = 1)
      ) AS a

INNER JOIN u_profile AS u ON u.user_id_login = a.friend_id

结果集将仅包含来自友谊和个人资料的行，其中存在与用户匹配的个人资料。结果将是:

FRIEND_ID  USERNAME
2          john doe
3          jane doe

W3schools还有一个关于不同连接类型的快速示例。