题解 | #获得积分最多的人(三)#

SELECT distinct id, name, score FROM
(SELECT id, name, final_score as score, DENSE_RANK() over(order by final_score DESC) as rk FROM
(SELECT id, name, jf+kf_new as final_score from(
SELECT x.id, x.name, jf, IFNULL(kf,0) as kf_new FROM
(SELECT id, name, sum(grade_num) over(partition by user_id) as jf from user
join grade_info on user.id = grade_info.user_id
WHERE type = 'add') x
left JOIN
(SELECT id, name, -sum(grade_num) over(partition by user_id) as kf from user
join grade_info on user.id = grade_info.user_id
WHERE type = 'reduce') y
ON x.id = y.id) fin) fin_new) a
WHERE rk = 1
order BY id