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

#请你写一个SQL查找积分增加最高的用户的id(可能有多个)，名字，以及他的总积分是多少，查询结果按照id升序排序

#窗口累加/group by,group by会导致name列不能加入子查询



/* with m1 as
(
select
sum(grade_num) as sum1
,g.user_id as user_id1
from grade_info g left join user u 
on g.user_id = u.id
where type='add'
group by g.user_id
)
#group by会导致name列不能加入子查询从而影响输出
select 
user_id1
from m1
where sum1 in(select max(sum1) from m1 )
*/


with m1 as
(
select
sum(grade_num)over(partition by u.name order by g.user_id asc) as sum1
#此时必须得name 来搭桥，单单g.user_id 不够达成窗口累加
,u.name as name1
,u.id as user_id1
from grade_info g left join user u 
on g.user_id = u.id
where type='add'
)

select 
distinct m1.user_id1 as id
, m1.name1 as name
, m1.sum1 as grade_num
from m1
where m1.sum1 in(select max(sum1) from m1)
order by id