AlgorithmJourney/LeetCode/sql/LeetCode1264.sql at main · HenryJi529/AlgorithmJourney · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
-- 用UNION拼接好友列表
select DISTINCT
    page_id as "recommended_page"
from Likes
where
    user_id in (
        select user1_id as "friend"
        from Friendship
        where
            user2_id = 1
        union
        select user2_id as "friend"
        from Friendship
        where
            user1_id = 1
    )
    and page_id not in(
        select page_id
        from Likes
        where
            user_id = 1
    );

-- 在select中选择好友列名
select DISTINCT
    page_id as "recommended_page"
from Likes
where
    user_id in (
        SELECT (
                CASE
                    WHEN user1_id = 1 then user2_id
                    WHEN user2_id = 1 then user1_id
                END
            ) AS user_id
        FROM Friendship
        WHERE
            user1_id = 1
            OR user2_id = 1
    )
    and page_id not in(
        select page_id
        from Likes
        where
            user_id = 1
    );