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

/*
#请你编写一个SQL，查询向user_id = 1 的用户，推荐其关注的人喜欢的音乐。不要推荐该用户已经喜欢的音乐，并且按music的id升序排列
select
*
from
(select
f.follower_id as f1
,m1.music_id as f1id
from follow f cross join music_likes m1
where f.user_id=1 and m1.user_id=1) as a1
left join music_likes m2
on a1.f1 =m2.user_id
left join music m3
on m2.music_id = m3.id
where a1.f1id<>m2.music_id

#这样判断有问题，因为笛卡尔积和左连接都会导致重复行增多，这样就会出现用户1喜欢的Aid≠关注用户喜欢的Bid，但是在别的行，用户1喜欢的AID＝关注用户喜欢的musicid,必须在连接前就把用户1喜欢的music_id直接筛掉。

例子：follow user_id=1 笛卡尔积 music_likes 再 左连 music_likes

1 2 17 18
1 4 17 17

虽然 17=17被剔除了，但是17≠18被保留了，死了

#交叉连接不能直接跟Left join
*/



/*请你编写一个SQL，查询向user_id = 1 的用户，推荐其关注的人喜欢的音乐。
不要推荐该用户已经喜欢的音乐，并且按music的id升序排列。你返回的结果中不应当包含重复项
上面的查询结果如下:

一步一步来

#查user_id=1的用户
select * from
where follow user_id =1
#推荐其关注的人喜欢的音乐,此时只剩user_id=1
select 
m1.music_id
from follow f left join music_likes m1
on f.follow_id=m1.music_id
where f.user_id=1
#不要推荐该用户已经喜欢的音乐，直接where筛掉
select 
m1.music_id
from follow f left join music_likes m1
on f.follow_id=m1.music_id
where f.user_id=1 and  m1.music_id not in (select 
music_id from music_likes where user_id =1
 )

*/

/* 法一
select 
m2.music_name as music_name
from follow f left join music_likes m1
on f.follower_id=m1.user_id
left join music m2
on m1.music_id=m2.id
where f.user_id=1 and  m1.music_id not in (select 
music_id from music_likes where user_id =1
 )
 group by m1.music_id
order by m1.music_id

减少left join 的使用
筛选出被关注者喜欢且剔除关注者喜欢的歌曲id之后直接和music表连接
*/ 

select
 m1.music_name as music_name
from
( select
distinct music_id
#被关注者喜欢的歌可能相同，在最外层 order by 和distinct 一起用会出Bug
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 
#user_id<>1 没有考虑到一种情况，就是关注者和被关注者喜欢同一首歌，最全面的剔除应该把user_id=1
#的人喜欢的歌直接就从music_name里面删了
left join music m1
on a.music_id=m1.id
order by m1.id

我真是笨笨的

distinct 和 order by 不能一起用也要分情况

因为distinct 是先于order by 执行的，在执行时候会产生一张虚拟表，order by会对这张虚拟表进行排序，所以如果order by后面的字段不在这张虚拟表中，就会报错，如果order by后的字段在 distinct后的字段，也就是新产生的虚拟表中，不会