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

with 
	user_like as (
select
			music.id,
			music_likes.user_id,
			music_likes.music_id,
			music.music_name
from
			music_likes
left join music on
			music.id = music_likes.music_id
right join follow on 
			follow.user_id = music_likes.user_id
	OR 
			follow.follower_id = music_likes.user_id
where follow.user_id=1 -- 查询用户喜欢和用户关注喜欢的所有歌曲
	),

	target_user_like as (
select 
			music_id,
			music_name
from 
			user_like
where
	user_id = 1
group by
	music_id,
	music_name
	),
	
	follow_user_like as (
	SELECT 
				music_id,
			music_name
from 
			user_like
where
	user_id != 1
group by music_id, music_name
	)
	
	

	select music_name from (SELECT  music_id,music_name
FROM follow_user_like A
WHERE NOT EXISTS (
    SELECT 1 
    FROM target_user_like B
    WHERE A.music_id = B.music_id
)
order by music_id asc) as s1;

使用cte，一步一步的拆解就行了