网易云音乐推荐(网易校招笔试真题)

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

原站题解

上次编辑到这里，代码来自缓存点击恢复默认模板

Sqlite 解法, 执行用时: 11ms, 内存消耗: 4324KB, 提交时间: 2022-03-02


SELECT DISTINCT m.music_name 
FROM follow f, music_likes ml, music m 
WHERE f.user_id = 1 
AND f.follower_id = ml.user_id 
AND ml.music_id = m.id
AND ml.music_id NOT IN (SELECT b.music_id FROM music_likes b WHERE b.user_id =1)
ORDER BY m.id ASC

Sqlite 解法, 执行用时: 12ms, 内存消耗: 3712KB, 提交时间: 2022-03-05

with t1 as (select ml.music_id as music_id
from follow f
join music_likes ml
on f.follower_id = ml.user_id
where f.user_id = 1
AND ml.music_id not in (
    select music_id from music_likes 
    where user_id = 1)
            )
            
select   DISTINCT ( m.music_name )
from music m
join t1 
on m.id = t1.music_id
order by m.id asc

Sqlite 解法, 执行用时: 12ms, 内存消耗: 3712KB, 提交时间: 2022-01-22

select distinct(m.music_name) as music_name from follow f 
join music_likes l 
on f.follower_id = l.user_id
and f.user_id = 1
join music m 
on l.music_id = m.id
where m.music_name not in 
(select music_name from music_likes lik 
 join music on music.id = lik.music_id
 where user_id = 1
)
order by m.id asc

Sqlite 解法, 执行用时: 12ms, 内存消耗: 3720KB, 提交时间: 2021-12-07

SELECT DISTINCT m.music_name 
FROM follow f, music_likes ml, music m 
WHERE f.user_id = 1 
AND f.follower_id = ml.user_id 
AND ml.music_id = m.id
AND ml.music_id NOT IN (SELECT b.music_id FROM music_likes b WHERE b.user_id =1)
ORDER BY m.id ASC

Sqlite 解法, 执行用时: 12ms, 内存消耗: 3760KB, 提交时间: 2021-11-29

select distinct m.music_name
from music_likes ml
join music m
on ml.music_id = m.id
where ml.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 ml.music_id

上一题

下一题

详情