【大厂真题】SQL24题解 | 网易云音乐推荐(网易校招笔试真题)

# 需求分析 user_id = 1
# 不包含用户已经喜欢的音乐
# 推荐的人喜欢的音乐
# 排序，music的id



with tmp as
(
    SELECT b.music_id
         from follow a
    left join music_likes b
           on a.user_id=b.user_id
    left join music c
           on b.music_id=c.id
    where a.user_id='1'
)

SELECT  c.music_name
     from follow a
left join music_likes b
       on a.follower_id=b.user_id
left join music c
       on b.music_id=c.id
left join tmp as d 
       on b.music_id=d.music_id
    where a.user_id='1'
      and d.music_id is null
group by b.music_id
order by b.music_id asc

不能distinct，但是实际代码可以运行，用id来判断

转载记录

原来我是将distinct和order by一起使用,但是发现运行报错:

SQL_ERROR_INFO: "Expression #1of ORDER BY clause is not in SELECT list,

references column 'm.id'which is not in SELECT list; thisis incompatible with DISTINCT"

查了一下,发现这是因为先执行distinct去重产生虚拟表,在此虚拟表的基础上进行order by,由于虚拟表没有order by的字段,产生报错

也就是order by的字段必须在select中出现

原代码如下:

select distinct music_name

from follow t1 join music_likes t2 on follower_id=t2.user_id

join music t3 on t2.music_id=t3.id

where t1.user_id=1and t3.music_name not in (

select music_name

from music_likes t2 join music t3 on t2.music_id=t3.id

where t2.user_id=1)

order by t2.music_id

由于报错,用group by替代distinct:

select music_name

from follow t1 join music_likes t2 on follower_id=t2.user_id

join music t3 on t2.music_id=t3.id

where t1.user_id=1and t3.music_name not in (

select music_name

from music_likes t2 join music t3 on t2.music_id=t3.id

where t2.user_id=1)

group by t2.music_id

order by t2.music_id

如果一定要用distinct也可以这么写:

select music_name

from (

select distinct music_name,id

from follow t1 join music_likes t2 on follower_id=t2.user_id

join music t3 on t2.music_id=t3.id

where t1.user_id=1) t4

where t4.music_name not in (

select music_name

from music_likes t2 join music t3 on t2.music_id=t3.id

where t2.user_id=1)

order by t4.id

题解 | #网易云音乐推荐#

发表于 2021-08-27 17:07

一步一步拆解问题，最后组合起来。

查询user_id = 1 的用户，其关注的人（follower_id ）1select follower_id from follow where user_id = 1;
查询user_id = 1 的用户，其关注的人喜欢的音乐（music_id）12select music_id from music_likeswhere user_id in (select follower_id from follow where user_id = 1);
查询user_id = 1 的用户，其关注的人喜欢的音乐，同时排除该用户已经喜欢的音乐（music_id）123select music_id from music_likeswhere 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);
查询user_id = 1 的用户，其关注的人喜欢的音乐，同时排除该用户已经喜欢的音乐（music_id）。接着，连接表music，获得音乐name，并且按music的id升序排列，返回的结果不包含重复项。即最终的代码为：复制代码12345678910selectdistinct music_namefrom (select music_id from music_likeswhere 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)) ajoin music m on a.music_id = m.idorder by id;