题解 | #获得积分最多的人(三)#
获得积分最多的人(三)
http://www.nowcoder.com/practice/d2b7e2a305a7499fb310dc82a43820e8
第一步，做计算
SELECT user_id,SUM(CASE type WHEN 'add' THEN grade_num
ELSE -1*grade_num END) grade_sum FROM grade_info g
group by user_id第二步，窗口函数排序
SELECT u.id,u.name,grade_sum,RANK()OVER(ORDER BY g1.grade_sum DESC ) AS R
FROM (SELECT user_id,SUM(CASE type WHEN 'add' THEN grade_num
ELSE -1*grade_num END) grade_sum FROM grade_info g
group by user_id) g1
第三步，联立表user
SELECT u.id,u.name,grade_sum,RANK()OVER(ORDER BY g1.grade_sum DESC ) AS R
FROM (SELECT user_id,SUM(CASE type WHEN 'add' THEN grade_num
ELSE -1*grade_num END) grade_sum FROM grade_info g
group by user_id) g1
JOIN user u ON g1.user_id=u.id) g2第四步，调取结果
SELECT id,name,grade_sum
FROM (SELECT u.id,u.name,grade_sum,RANK()OVER(ORDER BY g1.grade_sum DESC ) AS R
FROM (SELECT user_id,SUM(CASE type WHEN 'add' THEN grade_num
ELSE -1*grade_num END) grade_sum FROM grade_info g
group by user_id) g1
JOIN user u ON g1.user_id=u.id) g2
WHERE R=1