问题描述:向用户 user_id = 1 推荐其关注的人喜欢的音乐
有如下三个表:
- 用户关注表 follow
- 用户喜欢的音乐 music_likes
- 音乐名字表 music
create table follow
(
user_id int,
follower_id int
);
insert into follow values (1,2);
insert into follow values (1,4);
insert into follow values (1,5);
create table music_likes
(
user_id int,
music_id int
);
insert into music_likes values (1,20);
insert into music_likes values (1,30);
insert into music_likes values (1,40);
insert into music_likes values (2,10);
insert into music_likes values (2,20);
insert into music_likes values (2,30);
insert into music_likes values (4,10);
insert into music_likes values (4,20);
insert into music_likes values (4,30);
insert into music_likes values (4,60);
create table music
(
id int,
music_name char(10)
);
insert into music values (10,'a');
insert into music values (20,'b');
insert into music values (30,'c');
insert into music values (40,'d');
insert into music values (50,'e');
insert into music values (60,'f');
select music_name from music where
id in
(
select distinct music_id from music_likes where user_id in
(
select follower_id from follow where user_id = 1
)
)
and id not in
(
select music_id from music_likes where user_id = 1
)
order by id asc
;
推荐结果: