SQL查询返回特定用户的所有媒体，并检查朋友是否看过媒体

Question

Media Table：

257

id	user_id
1	2
2
3	2
4	10
5
6	7
7
8	1
9	2
10	2
11	1
12	8
13	2

你可以通过只对表进行一次操作来做到这一点，但你需要用想要的 id 来分割 on 子句

CREATE TABLE Media  (
`id` INTEGER,
`user_id` INTEGER
);
INSERT INTO Media 
(`id`, `user_id`)
VALUES
('1', '2'),
('2', '2'),
('3', '2'),
('4', '10'),
('5', '5'),
('6', '7'),
('7', '7'),
('8', '1'),
('9', '2'),
('10', '2'),
('11', '1'),
('12', '8'),
('13', '2');

CREATE TABLE Seen  (
`id` INTEGER,
`user_id` INTEGER,
`media_id` INTEGER,
`friend_id` INTEGER
);
INSERT INTO Seen 
(`id`, `user_id`, `media_id`, `friend_id`)
VALUES
('1', '2', '2', '5'),
('2', '2', '3', '5'),
('3', '2', '10', '10'),
('4', '10', '7', '5'),
('5', '2', '13', '5'),
('6', '7', '24', '7'),
('7', '7', '23', '9'),
('8', '1', '26', '1'),
('9', '7', '22', '2'),
('10', '9', '19', '2'),
('11', '1', '22', '2'),
('12', '8', '20', '2'),
('13', '20', '14', '5'),
('14','2','2', '4');

SELECT m.user_id, m.id as media_id, s.friend_id 
FROM Media m LEFT JOIN Seen s ON s.user_id = m.user_id AND m.id = s.media_id AND s.friend_id = 5
WHERE m.user_id = 2 

user_id | media_id | friend_id ------: |-------: |--------: 2 |       1 |空2 |       2 |        5 2 |       3 |        5 2 |       9 |空2 |      10 |空2 |      13 |        5

db<>在这里小提琴