题解 | #获取员工其当前的薪水比其manager当前薪水还高的相关信息#
获得积分最多的人(三)
http://www.nowcoder.com/practice/d2b7e2a305a7499fb310dc82a43820e8
select t.id, t.name, t.grade_sum
from
(select u.id,u.name,
sum(g.grade_num * case when type = 'add' then 1 else -1 end) as grade_sum,
dense_rank() over(order by sum(g.grade_num * case when type = 'add' then 1 else -1 end) desc) as idx
from user u, grade_info g
where u.id = g.user_id
group by u.id, u.name) t
from
(select u.id,u.name,
sum(g.grade_num * case when type = 'add' then 1 else -1 end) as grade_sum,
dense_rank() over(order by sum(g.grade_num * case when type = 'add' then 1 else -1 end) desc) as idx
from user u, grade_info g
where u.id = g.user_id
group by u.id, u.name) t
where t.idx = 1
1. case when 把add 改成1， reduce 改成-1
2. 分数和case when的结果按组累加得到总分，同时计算排名
3. 取出总分排名最高的idx=1的所选字段
投递美团等公司10个岗位 >
查看10道真题和解析