关注follow表,第一列是关注人的id,第二列是被关注人的id,这2列的id组成主键
user_id | follower_id |
1 | 2 |
1 | 4 |
2 | 3 |
这张表的第一行代表着用户id为1的关注着id为2的用户
这张表的第二行代表着用户id为1的关注着id为4的用户
这张表的第三行代表着用户id为2的关注着id为3的用户
个人的喜欢的音乐music_likes表,第一列是用户id,第二列是喜欢的音乐id,这2列的id组成主键
user_id | music_id |
1 | 17 |
2 | 18 |
2 | 19 |
3 | 20 |
4 | 17 |
这张表的第一行代表着用户id为1的喜欢music_id为17的音乐
....
这张表的第五行代表着用户id为4的喜欢music_id为17的音乐
音乐music表,第一列是音乐id,第二列是音乐name,id是主键
id | music_name |
17 | yueyawang |
18 | kong |
19 | MOM |
20 | Sold Out |
请你编写一个SQL,查询向user_id = 1 的用户,推荐其关注的人喜欢的音乐。
不要推荐该用户已经喜欢的音乐,并且按music的id升序排列。你返回的结果中不应当包含重复项
上面的查询结果如下:
music_name |
kong |
MOM |
示例1
输入
CREATE TABLE `follow` ( `user_id` int(4) NOT NULL, `follower_id` int(4) NOT NULL, PRIMARY KEY (`user_id`,`follower_id`)); CREATE TABLE `music_likes` ( `user_id` int(4) NOT NULL, `music_id` int(4) NOT NULL, PRIMARY KEY (`user_id`,`music_id`)); CREATE TABLE `music` ( `id` int(4) NOT NULL, `music_name` varchar(32) NOT NULL, PRIMARY KEY (`id`)); INSERT INTO follow VALUES(1,2); INSERT INTO follow VALUES(1,4); INSERT INTO follow VALUES(2,3); INSERT INTO music_likes VALUES(1,17); INSERT INTO music_likes VALUES(2,18); INSERT INTO music_likes VALUES(2,19); INSERT INTO music_likes VALUES(3,20); INSERT INTO music_likes VALUES(4,17); INSERT INTO music VALUES(17,'yueyawang'); INSERT INTO music VALUES(18,'kong'); INSERT INTO music VALUES(19,'MOM'); INSERT INTO music VALUES(20,'Sold Out');
输出
kong MOM
第一步:用户1关注了哪些人 select follower_id from follow where user_id=1 第二步:用户1喜欢的音乐 select music_id from music_likes where user_id=1 第三步:关注的那些人喜欢的音乐 select music_id from music_likes where user_id in ( select follower_id from follow where user_id=1 ) 第四步:除了用户1喜欢的音乐外,关注的那些人喜欢的其他音乐 select music_id from music_likes where user_id in ( select follower_id from follow where user_id=1 ) and music_id not in( select music_id from music_likes where user_id=1 ) 第五步:与音乐表联结 select distinct m.music_name from music m join ( select music_id from music_likes where user_id in ( select follower_id from follow where user_id=1 ) and music_id not in( select music_id from music_likes where user_id=1 ) )a on a.music_id=m.id order by m.id
编辑于 2021-05-28 22:22:15
回复(4)
分别查找 用户1 及其关注者喜欢的音乐,则最终的结果使用 IN 、NOT IN 寻找并排除
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 DISTINCT music_id
FROM music_likes
WHERE user_id=1)
发表于 2021-03-11 22:54:13
回复(5)
select music_name from music left join music_likes on music.id = music_likes.music_id left join follow on music_likes.user_id =follower_id where follow.user_id = 1 and music_name not in (select music_name from music left join music_likes on music.id = music_likes.music_id left join follow on music_likes.user_id =follow.user_id where follow.user_id = 1) group by music_id order by music_id这道题的难点在于distinct与order by 子句一起运行会报错,可以使用group by 子句代替distinct解决.
发表于 2022-01-11 16:41:13
回复(3)
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;利用子查询,但需要注意null的处理,最好使用NOT IN剔除自己喜欢的音乐,而不是<>。当自己没有喜欢的音乐时候,使用不等于进行剔除就可能出现问题。
编辑于 2021-02-16 21:07:47
回复(0)
一开始用了join。 发现order by和distinct冲突的问题。不好弄
然后参考了大佬的程序使用子查询做出来了
记录一下我学习到的东西
先给一下代码
select music_name from music where id in ( select 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
- 这个问题一个简单的地方在于确定user_id=1 算是方便了不少
- 由于是直接从music里选取,所以id与music_name一一对应,既不用去重,而且还解决了order by与distinct不独立的问题
- 大佬真的赞
发表于 2022-07-01 22:47:42
回复(0)
注意要去重!
前面做的获得积分最多的人系列的题做到自闭,这道题很快做出来了,开心!
刷题下来有一个经验就是:一定要想好再写!确定思路了再写!千万不要在自己对题目的理解都一知半解的情况下胡乱写~
这道题的思路:先确定下我关注的人和我喜欢的音乐的id,并去掉我和关注的人都喜欢的id,再用music表将id对上名字,需要注意的是,可能我关注的人中喜欢的音乐有重叠,因此要对music_name去重~
select distinct t3.music_name from follow t1, music_likes t2, music t3 where t1.follower_id = t2.user_id and t2.music_id = t3.id and t1.user_id =1 and t2.music_id not in (select music_id from music_likes where user_id =1) order by t3.id
发表于 2021-10-06 20:56:09
回复(4)
先将三表按照顺序左连接,找到用户感兴趣的音乐;然后再左连接用户已喜爱的音乐,则用户可能感兴趣的、未喜爱的音乐会在右表出现空值,筛选出有空值的行,对音乐进行去重即可得到最终结果。
select music_name from ( select distinct m.id, m.music_name from follow f left join music_likes ml on f.follower_id=ml.user_id left join music m on ml.music_id=m.id left join music_likes ml2 on f.user_id=ml2.user_id and ml.music_id=ml2.music_id where f.user_id=1 and ml2.user_id is null order by m.id asc ) x;
发表于 2022-07-05 20:55:31
回复(0)
mysql里select distinct后面没有的列不能排序,所以得先去重,然后派生表再排序
select music_name from (select distinct c.id,c.music_name from follow a join music_likes b on a.follower_id=b.user_id join music c on b.music_id=c.id where a.user_id=1 and b.music_id not in (select music_id from music_likes where user_id=1) ) x order by id
发表于 2022-01-26 15:47:50
回复(1)
with res as ( select music_id as id from music_likes where user_id in( select follower_id from follow where user_id=1 ) and music_id not in( select music_id from music_likes where user_id=1 ) ) select music_name FROM music where id IN(select * from res);
发表于 2021-10-21 12:09:17
回复(1)
SELECT DISTINCT m.music_name FROM music m,music_likes ml WHERE m.id = ml.music_id AND ml.user_id IN (SELECT follower_id FROM follow WHERE user_id = 1) AND m.id NOT IN (SELECT music_id FROM music_likes WHERE user_id = 1) ORDER BY m.id
发表于 2021-10-21 10:35:43
回复(0)
with t as ( select d.user_id,d.music_id from music_likes d inner join music c on d.music_id=c.id ) select distinct music_name from (select a.user_id, c.music_name,c.id from follow a inner join music_likes b on a.follower_id=b.user_id inner join music c on b.music_id=c.id where a.user_id=1) a where a.id not in (select t.music_id from t where t.user_id=a.user_id) order by a.id
发表于 2021-09-29 21:57:28
回复(0)
贡献一种新的解法,针对每一个表进行查询,像套娃一样的一个表一个表嵌套查询,主要在from语句后面
select distinct music_name
from (select music_id from music_likes where user_id in (select follower_id from follow where user_id=1)) as b left join
music m
on m.id=b.music_id
where m.id not in (select music_id from music_likes where user_id=1 )
order by m.id asc
编辑于 2021-05-11 15:32:33
回复(2)
select music_name from music_likes left join music on music_likes.music_id = music.id where user_id in (select follower_id from follow where user_id = 1) and music_id not in (select music_id from music_likes where user_id = '1') group by music_name order by any_value(music_likes.music_id)
发表于 2022-10-01 12:26:56
回复(0)
with a1 as( select music_id from follow join music_likes on follower_id = music_likes.user_id where follow.user_id = 1 ), #关注人喜欢音乐的ID a2 as( select music_id from music_likes where user_id=1 ) #自己喜欢的音乐ID select music_name from music where id in (select * from a1) and id not in (select * from a2)
发表于 2022-06-19 15:12:55
回复(0)
以follow为主表,左连接另外两张表,得到关注者的歌单,再从其中剔除自己喜欢的音乐即可
select distinct m.music_name from follow f left join music_likes ml on f.follower_id=ml.user_id left join music m on ml.music_id=m.id where m.music_name not in ( select music_name from follow join music_likes on follow.user_id=music_likes.user_id join music on music_likes.music_id=music.id where follow.user_id=1 ) and f.user_id=1 order by m.id
发表于 2021-01-08 17:38:27
回复(0)
有大佬帮我看看错误吗
SELECT music_name from music where id IN((select music_id from music_likes WHERE user_id IN (select follower_id from follow WHERE user_id=1))
except (select music_id from music_likes WHERE user_id=1))
发表于 2024-02-20 10:12:49
回复(0)
# 请你编写一个SQL,查询向user_id = 1 的用户,推荐其关注的人喜欢的音乐。 # 不要推荐该用户已经喜欢的音乐,并且按music的id升序排列。你返回的结果中不应当包含重复项 # 方法一:使用not in # with dwd_data as ( # select # distinct # t3.id, # t3.music_name # from follow t1 # left join music_likes t2 # on t1.follower_id = t2.user_id # left join music t3 on t2.music_id = t3.id # where t1.user_id = 1 and t2.music_id not in( select music_id from music_likes where user_id=1) # ) # select music_name from dwd_data order by id # 方法二:使用关联,注意剔除相同的音乐 with dwd_data as ( select distinct t3.id, t3.music_name from follow t1 left join music_likes t2 on t1.follower_id = t2.user_id left join music t3 on t2.music_id = t3.id left join music_likes t4 on t4.user_id=1 and t2.music_id= t4.music_id where t1.user_id = 1 and t4.music_id is null ) select music_name from dwd_data order by id;
发表于 2023-11-01 11:25:39
回复(0)