通过问题学习

SQL

三张表，user, video, favorite。求两个用户都点赞的视频

初始化

create table "user"
(
    id       int primary key GENERATED ALWAYS AS IDENTITY,
    username varchar(255) not null,
    password varchar(255) not null,
    email    varchar(255) not null
);

create table video
(
    id          int primary key GENERATED ALWAYS AS IDENTITY,
    title       varchar(255) not null,
    description varchar(255) not null,
    url         varchar(255) not null
);

create table favorite
(
    user_id  int not null,
    video_id int not null
);

create index user_video_index on favorite (user_id, video_id);
create index video_user_index on favorite (video_id, user_id);

insert into "user" (username, password, email)
values ('admin', 'admin', 'saa'),
       ('user', 'user', 'd'),
       ('user2', 'user2', 'd2');

insert into video (title, description, url)
values ('video1', 'desc1', 'url1'),
       ('video2', 'desc2', 'url2'),
       ('video3', 'desc3', 'url3');

insert into favorite (user_id, video_id)
values (1, 1),
       (1, 2),
       (2, 1),
       (2, 3),
       (3, 2),
       (3, 3);

方案一：使用 subquery

先查询到用户 1 点赞的视频，再使用 where 查询用户 2 点赞的视频，使用 in 进行过滤。

select favorite.video_id from favorite where user_id = 2 and video_id in (select video_id from favorite where user_id=1);

方案二：使用 join

通过 favorite 表自连接，找到两个用户都点赞的视频。

select f2.video_id from favorite f1 join favorite f2 on f1.video_id = f2.video_id where f1.user_id = 1 and f2.user_id = 2;