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

-- 链接 LINK: https://www.nowcoder.com/practice/048ed413ac0e4cf4a774b906fc87e0e7?tpId=82&tqId=38864&rp=1&ru=/exam/oj&qru=/exam/oj&sourceUrl=%2Fexam%2Foj%3Fpage%3D1%26pageSize%3D50%26search%3D%26tab%3DSQL%25E7%25AF%2587%26topicId%3D82&difficulty=undefined&judgeStatus=undefined&tags=&title=
-- 标题 TITLE: SQL287 网易云音乐推荐(网易校招笔试真题)
-- 日期 DATE: 2023-02-25

/* 设计思路

**/

-- 题解 SQL
SELECT T2.music_name
FROM (
    SELECT user_id, follower_id FROM follow
    WHERE user_id = 1
) T1
INNER JOIN (
    SELECT TA.music_id, TB.music_name, TA.user_id FROM music_likes TA
    INNER JOIN music TB ON TA.music_id = TB.id
) T2 ON T2.user_id = T1.follower_id
LEFT JOIN music_likes T3 ON T3.user_id = T1.user_id AND T3.music_id = T2.music_id
WHERE T3.music_id IS NULL
GROUP BY T2.music_id, T2.music_name
ORDER BY T2.music_id

;

-- 数据 DATA ===================================================
DROP TABLE IF EXISTS follow;

DROP TABLE IF EXISTS music_likes;

DROP TABLE IF EXISTS music;
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');