MySQL:从 INNER JOIN 内部分组

Question

这里是 MySQL 5.6。我有以下表格:

DESCRIBE profiles;
+----------------------------+---------------------+------+-----+---------+----------------+
| Field                      | Type                | Null | Key | Default | Extra          |
+----------------------------+---------------------+------+-----+---------+----------------+
| profile_id                 | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| profile_given_name         | varchar(100)        | YES  | MUL | NULL    |                |
| profile_surname            | varchar(100)        | YES  | MUL | NULL    |                |
+----------------------------+---------------------+------+-----+---------+----------------+

describe friendships;
+----------------------+---------------------+------+-----+---------+----------------+
| Field                | Type                | Null | Key | Default | Extra          |
+----------------------+---------------------+------+-----+---------+----------------+
| friendship_id        | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| requester_profile_id | bigint(20) unsigned | NO   | MUL | NULL    |                |
| recipient_profile_id | bigint(20) unsigned | NO   | MUL | NULL    |                |
+----------------------+---------------------+------+-----+---------+----------------+

profiles 表代表系统的用户，friendships 代表多对多关系，其中用户与其他用户是 friend 。当用户/配置文件向用户发送“好友请求”时，他们被视为好友的请求者。相反，那些从其他人那里收到 friend 请求的人是收件人。

我正在尝试编写一个(看似)简单的符合 ANSI 标准的 SELECT，它告诉我特定配置文件的所有配置文件是好友，无论它们是否是 请求者 或友谊的接收者。

用一些数据设置数据库:

INSERT INTO friendships (
  friendship_ref_id,
  requester_profile_id,
  recipient_profile_id
) VALUES (
  '01234',
  2,
  1
), (
  '67890',
  1,
  3  
), (
  '78901',
  1,
  4
), (
  '89012',
  2,
  3
), (
  '90123',
  3,
  4
);

那么我迄今为止最好的尝试:

SELECT      f.requester_profile_id,
            f.recipient_profile_id
FROM        profiles p
INNER JOIN  friendships f
ON (
    f.requester_profile_id = p.profile_id
    OR
    f.recipient_profile_id = p.profile_id
)
WHERE       p.profile_id = 1;

当我运行它时，我得到:

+----------------------+----------------------+
| requester_profile_id | recipient_profile_id |
+----------------------+----------------------+
|                    1 |                    2 |
|                    1 |                    3 |
|                    1 |                    4 |
|                    2 |                    1 |
+----------------------+----------------------+

但我真正想要的是由 profile_id = 1 的所有 DISTINCT 配置文件 ID(类似的请求者和接收者)组成的一个列是 friend ，比如:

+----------------------+
|      profile_friends |
+----------------------+
|                    2 |
|                    3 |
|                    4 |
+----------------------+

...因为 profile_id = 1 是 profile_id IN { 2, 3, 4 } 等的 friend

我在这里缺少什么才能使查询正常工作？

Answer 1

这里有两个选项。

第一个使用 CASE 来决定每个结果使用哪一列:

SELECT     
   CASE WHEN f.requester_profile_id = p.profile_id THEN f.recipient_profile_id 
        ELSE f.requester_profile_id END as profile_friends
FROM        profiles p
INNER JOIN  friendships f
  /* Original ON clause would still work, but I prefer the briefer syntax */
ON p.profile_id IN (f.requester_profile_id,f.recipient_profile_id)
WHERE       p.profile_id = 1;

第二个 UNION 将两个 SELECT 语句组合在一起，其中一个检查请求者，另一个检查接收者:

SELECT      f.recipient_profile_id as profile_friends
FROM        profiles p
INNER JOIN  friendships f
ON f.requester_profile_id = p.profile_id
WHERE       p.profile_id = 1

UNION

SELECT      f.requester_profile_id
FROM        profiles p
INNER JOIN  friendships f
ON  f.recipient_profile_id = p.profile_id
WHERE       p.profile_id = 1;