关注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
SELECT music_name FROM( SELECT DISTINCT ml.music_id, m.music_name FROM music m JOIN music_likes ml ON ml.music_id = m.id WHERE 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 ) ) AS m2 ORDER BY music_id;
发表于 2024-05-19 12:52:20
回复(0)
with f1 as(select follower_id from follow where user_id=1) #id1关注的id ,id1 as (select music_id from music_likes where user_id=1) #id1喜欢的音乐 ,idm as (select distinct music_id from music_likes where user_id in (select follower_id from f1)) #关注的id喜欢的音乐 select music_name from idm left join music m1 on idm.music_id=m1.id where idm.music_id not in (select music_id from id1 ) order by m1.id
发表于 2024-04-25 11:46:46
回复(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))
and id not in(
select music_id from music_likes
where user_id=1
)
order by id
直接嵌套子查询 因为 主表就是music 直接orderby id 不需要任何表连接
发表于 2024-04-17 18:21:07
回复(0)
select music_name from music where id in (select sc.music_id from follow f left join music_likes sc on f.follower_id=sc.user_id where f.user_id=1) and id not in (select music_id from music_likes where user_id=1) order by id
发表于 2024-04-15 15:41:48
回复(0)
#用distinct select t.music_name from( select distinct music_name,music_id from follow as f inner join music_likes as m on f.follower_id=m.user_id inner join music as mm on m.music_id=mm.id where f.user_id=1 #用户1关注的人喜欢的音乐 and music_name not in #排除用户1喜欢的音乐 (select music_name from follow as f join music_likes as m on f.user_id=m.user_id join music as mm on m.music_id=mm.id where f.user_id = 1) ) as t #用户1喜欢的音乐 order by t.music_id;
用group by(不知道为啥distinct和order by提起用会报错)
select music_name from follow as f inner join music_likes as m on f.follower_id=m.user_id inner join music as mm on m.music_id=mm.id where f.user_id=1 #用户1关注的人喜欢的音乐 and music_name not in #排除用户1喜欢的音乐 (select music_name from follow as f join music_likes as m on f.user_id=m.user_id join music as mm on m.music_id=mm.id where f.user_id = 1) group by music_id order by music_id;因为用户1关注的人可能喜欢同一首音乐,所以需要用distinct或者group by去重
编辑于 2024-01-13 22:29:35
回复(0)
select bb.music_name from (select distinct 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')) aa left join music bb on aa.music_id=bb.id order by aa.music_id
编辑于 2024-01-03 16:08:23
回复(0)
# 关注人的id
select music_name from music where id in(select (music_id)
from( select * from follow where user_id = 1) t1
join music_likes ml on ml.user_id = t1.follower_id
join music where music.id = ml.music_id )
and id not in (
select (music_id) from music_likes where user_id = 1
)
order by music.id
# order by t1.user_id
select music_name from music where id in(select (music_id)
from( select * from follow where user_id = 1) t1
join music_likes ml on ml.user_id = t1.follower_id
join music where music.id = ml.music_id )
and id not in (
select (music_id) from music_likes where user_id = 1
)
order by music.id
# order by t1.user_id
发表于 2023-11-22 16:15:55
回复(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)
select music_name as music_name from music inner 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) ) t on music.id = t.music_id group by music.music_name,music.id order by music.id ASC;
发表于 2023-10-25 17:56:30
回复(0)
#方法1 select d.music_name from( select distinct b.music_id from follow a left join music_likes b on a.follower_id=b.user_id and b.music_id not in (select music_id from music_likes where user_id=1) where a.user_id=1 order by b.music_id ) c left join music d on c.music_id=d.id where d.music_name is not null #方法2 select music_name from ( select distinct 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) order by music_id ) a left join music b on a.music_id=b.id
发表于 2023-10-17 00:27:58
回复(0)
我想知道为什么不能group by music_name,不是说group by 之后跟的列应该是select后面的列吗
SELECT m.music_name
FROM follow f
INNER JOIN music_likes ml
ON f.follower_id=ml.user_id
INNER JOIN music m
ON ml.music_id=m.id
WHERE f.user_id=1
AND music_name NOT IN(
SELECT music_name
FROM follow f
INNER JOIN music_likes ml
ON f.user_id=ml.user_id
INNER JOIN music m
ON ml.music_id=m.id
WHERE f.user_id=1
)
GROUP BY ml.music_id
ORDER BY ml.music_id
FROM follow f
INNER JOIN music_likes ml
ON f.follower_id=ml.user_id
INNER JOIN music m
ON ml.music_id=m.id
WHERE f.user_id=1
AND music_name NOT IN(
SELECT music_name
FROM follow f
INNER JOIN music_likes ml
ON f.user_id=ml.user_id
INNER JOIN music m
ON ml.music_id=m.id
WHERE f.user_id=1
)
GROUP BY ml.music_id
ORDER BY ml.music_id
发表于 2023-10-14 21:23:01
回复(0)
我把以上所有的大佬都试了一遍,包括自己.都是多了2行sold out,这是题目出问题了吧
我的:
select music_name
from (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)) as a
INNER JOIN music as b
on a.music_id = b.id
order by b.id asc
from (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)) as a
INNER JOIN music as b
on a.music_id = b.id
order by b.id asc
发表于 2023-06-10 18:07:52
回复(0)