题解 | #获得积分最多的人(三)#
获得积分最多的人(三)
https://www.nowcoder.com/practice/d2b7e2a305a7499fb310dc82a43820e8
使用了蠢办法，建立一个新的独立列，用来设置+1/-1
然后就是跟前面同系列题目的方法拉过来With table1 as ( SELECT user_id,grade_num,type, CASE WHEN type = 'add' then 1 WHEN type = 'reduce' then -1 END as c1 FROM grade_info )
SELECT id,name,grade_num FROM( SELECT DISTINCT user_id as id,name,grade_num,DENSE_RANK() OVER (ORDER BY grade_num DESC) as rnk FROM( SELECT user_id,t2.name as name,sum(grade_num) over (PARTITION by name) as grade_num FROM( SELECT user_id,grade_num*c1 as grade_num FROM table1 ) as t1 INNER JOIN user as t2 ON t1.user_id = t2.id )as t3 )as t4 WHERE rnk = 1 ORDER BY id整个代码
With table1 as ( SELECT user_id,grade_num,type, CASE WHEN type = 'add' then 1 WHEN type = 'reduce' then -1 END as c1 FROM grade_info ) SELECT id,name,grade_num FROM( SELECT DISTINCT user_id as id,name,grade_num,DENSE_RANK() OVER (ORDER BY grade_num DESC) as rnk FROM( SELECT user_id,t2.name as name,sum(grade_num) over (PARTITION by name) as grade_num FROM( SELECT user_id,grade_num*c1 as grade_num FROM table1 ) as t1 INNER JOIN user as t2 ON t1.user_id = t2.id )as t3 )as t4 WHERE rnk = 1 ORDER BY id# 看了一楼大佬的想法，精辟，respect。
