题解 | #网易云音乐推荐(网易校招笔试真题)#
网易云音乐推荐(网易校招笔试真题)
http://www.nowcoder.com/practice/048ed413ac0e4cf4a774b906fc87e0e7
原来我是将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=1 and 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=1 and 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
投递大疆等公司10个岗位