1 题目描述

朋友关系列表： Friendship

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user1_id      | int     |
| user2_id      | int     |
+---------------+---------+

(user1_id, user2_id) 是这张表具有唯一值的列的组合。这张表的每一行代表着 user1_id 和 user2_id 之间存在着朋友关系。

喜欢列表： Likes

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| user_id     | int     |
| page_id     | int     |
+-------------+---------+

(user_id, page_id) 是这张表具有唯一值的列的组合。这张表的每一行代表着 user_id 喜欢 page_id。

编写解决方案，向user_id = 1 的用户，推荐其朋友们喜欢的页面。不要推荐该用户已经喜欢的页面。以 任意顺序 返回结果，其中不应当包含重复项。

2 测试用例

输入：

Friendship table:

+----------+----------+
| user1_id | user2_id |
+----------+----------+
| 1        | 2        |
| 1        | 3        |
| 1        | 4        |
| 2        | 3        |
| 2        | 4        |
| 2        | 5        |
| 6        | 1        |
+----------+----------+

Likes table:

+---------+---------+
| user_id | page_id |
+---------+---------+
| 1       | 88      |
| 2       | 23      |
| 3       | 24      |
| 4       | 56      |
| 5       | 11      |
| 6       | 33      |
| 2       | 77      |
| 3       | 77      |
| 6       | 88      |
+---------+---------+

输出：

+------------------+
| recommended_page |
+------------------+
| 23               |
| 24               |
| 56               |
| 33               |
| 77               |
+------------------+

解释： 用户1 同用户2, 3, 4, 6 是朋友关系。推荐页面为：页面23 来自于用户2, 页面24 来自于用户3, 页面56 来自于用户3 以及页面33 来自于用户6。页面77 同时被用户2 和用户3 推荐。页面88 没有被推荐，因为用户1 已经喜欢了它。

3 解题思路

查询 user_id = 1 的所有好友信息

SELECT user2_id AS user_id FROM Friendship where user1_id = 1  
UNION ALL  
SELECT user1_id AS user_id FROM Friendship where user2_id = 1

执行结果

+-------+
|user_id|
+-------+
|2      |
|3      |
|4      |
|6      |
+-------+

查询 user_id = 1 的所有好友喜欢的 page_id

select distinct page_id as recommended_page  
from Likes  
where user_id in (SELECT user2_id AS user_id FROM Friendship where user1_id = 1  
                  UNION ALL  
                  SELECT user1_id AS user_id FROM Friendship where user2_id = 1)

执行结果

+----------------+
|recommended_page|
+----------------+
|23              |
|24              |
|56              |
|33              |
|77              |
|88              |
+----------------+

排查 user_id = 1 喜欢的 page_id

select distinct page_id as recommended_page  
from Likes  
where user_id in (SELECT user2_id AS user_id FROM Friendship where user1_id = 1  
                  UNION ALL  
                  SELECT user1_id AS user_id FROM Friendship where user2_id = 1)  
  and page_id not in (select page_id from Likes where user_id = 1)

执行结果

+----------------+
|recommended_page|
+----------------+
|23              |
|24              |
|56              |
|33              |
|77              |
+----------------+

LeetCode--1264. 页面推荐

1 题目描述

2 测试用例

3 解题思路