题解 | #获得积分最多的人(二)#
获得积分最多的人(二)
http://www.nowcoder.com/practice/b6248d075d2d4213948b2e768080dc92
1.将grade_info表按照用户分数求和
select user_id,sum(grade_num) grades
from grade_info
group by user_id; t2
2.与user表关联
select id,name,grades from
t2 join user t1
on t1.id = t2.user_id; t3
3.给出排序（利用窗口函数）
select id,name,grades,rank() over(order by grades desc) rk
from t3；t4
4.输出最高
select id,name,grades from t4
where rk = 1
最终的sql
select id,name,grades from
(select id,name,grades,rank() over(order by grades desc) rk
from (select id,name,grades from
(select user_id,sum(grade_num) grades
from grade_info
group by user_id)t2 join user t1
on t1.id = t2.user_id)t3)t4
where rk = 1
