题目链接:点击打开链接
题目大意:略。
解题思路:略。
AC 代码
WITH t AS(SELECT user1_id user_id FROM Friendship UNION SELECT user2_id FROM Friendship), tt AS(SELECT user_id, user2_id friend_id FROM t JOIN Friendship ON user_id = user1_id UNION SELECT user_id, user1_id FROM t JOIN Friendship ON user_id = user2_id) SELECT tt.user_id, l1.page_id, COUNT(*) friends_likes FROM tt JOIN Likes l1 ON friend_id = l1.user_id LEFT JOIN Likes l2 ON l1.page_id = l2.page_id AND l2.user_id = tt.user_id WHERE l2.user_id IS NULL # 最后一个筛选自身已喜欢的 page 采用表连接, 不能用子查询, 否则超时 GROUP BY tt.user_id, l1.page_id