仅用JOIN查询ID=1的2度好友（直接+好友的好友）的FriendId

基础信息 #

原始好友关系表 #

表名：friend，字段说明：

• id：用户ID
• friend_id：对应用户的好友ID
  表数据：

id  friend_id
1    2
2    3
1    4
4    5
5    6

需求 #

查询用户ID为1的2度好友，包含直接好友（1度）和好友的好友（2度），排除用户自身，预期结果：

2
3
4
5

要求仅用JOIN实现，不可使用IN子查询、CTE、视图等语法。

原有写法问题 #

你之前提交的两种写法都只能获取到2度好友（3、5），漏掉了直接好友（2、4），同时使用了子查询和CTE，不符合要求。

正确实现 #

SQL语句 #

SELECT DISTINCT
  CASE WHEN n.row_id = 1 THEN f1.friend_id ELSE f2.friend_id END AS friend_id
FROM friend f1
LEFT JOIN friend f2
  ON f1.friend_id = f2.id
-- 构造两行虚拟数据，用于把两列好友ID拆成独立行
JOIN (SELECT 1 AS row_id UNION ALL SELECT 2 AS row_id) n
WHERE f1.id = 1
  -- 过滤空值和用户自身
  AND CASE WHEN n.row_id = 1 THEN f1.friend_id ELSE f2.friend_id END IS NOT NULL
  AND CASE WHEN n.row_id = 1 THEN f1.friend_id ELSE f2.friend_id END != 1
ORDER BY friend_id;

逻辑说明 #

1. 第一层表f1过滤f1.id=1，得到用户1的所有直接好友：f1.friend_id的值为2、4
2. 左连接第二层表f2，关联条件为f1.friend_id = f2.id，得到直接好友的好友：f2.friend_id的值为3、5
3. JOIN两行虚拟数据，将f1.friend_id和f2.friend_id两个列的数值拆为独立的行
4. 用DISTINCT去重后得到最终的2度好友列表，完全匹配预期结果

内容的提问来源于stack exchange，提问作者TintinSnowy