题解 | #获得积分最多的人(二)#
获得积分最多的人(二)
http://www.nowcoder.com/practice/b6248d075d2d4213948b2e768080dc92
select u.id,u.name,g.s from user u left join (select user_id, sum(grade_num) s from grade_info group by user_id) g on u.id = g.user_id
where g.s =
(select sum(grade_num) over(partition by user_id order by user_id) s_n
from grade_info order by s_n desc limit 1)
思路：先求出最大值
select sum(grade_num) over(partition by user_id order by user_id) s_n
from grade_info order by s_n desc limit 1
将两个表合并，求出各个id的sum值，通过where找出最大值
select u.id,u.name,g.s from user u left join (select user_id, sum(grade_num) s from grade_info group by user_id) g on u.id = g.user_id
where g.s =
(select sum(grade_num) over(partition by user_id order by user_id) s_n
from grade_info order by s_n desc limit 1)
