SQL91 获得积分最多的人(三)
select a.user_id, u.name, a.grade_sumfrom(select user_id, sum(if(type='add',grade_num,-1 * grade_num)) as grade_sum, dense_rank() over(order by sum(if(type='add',grade_num,-1 * grade_num)) desc) as `tank`from grade_infogroup by user_id) aleft join user u on a.user_id = u.idwhere `tank` = 1
解题思路：加一个判断，如果type是add，则加分，否则减分
SQL92 商品交易(网易校招笔试真题)
select t.goods_id, g.name, weight, t.cnt from ( select goods_id, sum(count) as cnt from trans group by goods_id having cnt > 20 ) t left join goods g on g.id = t.goods_id where weight < 50 order by t.goods_id
SQL93 网易云音乐推荐(网易校招笔试真题)
select music_name from( select distinct mc.id, music_name from follow f left join music_likes m on m.user_id = f.follower_id left join music mc on m.music_id = mc.id where f.user_id = 1 and mc.id not in ( select music_id from music_likes where user_id = 1 ) order by mc.id ) a
SQL94 今天的刷题量(一)
select name, count(create_time) as cnt from submission s left join subject su on s.subject_id = su.id where create_time = CURRENT_DATE() group by name, s.subject_id order by cnt desc, s.subject_id
