LC 1308. Running Total for Different Genders

题目链接

 

 

 

 

 

 

题解1：

先排序，然后用自定义变量记录累计的score。

select gender,day,total from(

    select day, 

    @s:=(if(gender=@g,@s+score_points,score_points)) as total,

    @g:=gender as gender 

    from

    (

        select gender,day, sum(score_points) as score_points from Scores group by gender, day 

        order by gender, day

    )t1, (select @s:=0,@g:=null) p

)t2

 

题解2:

内连接

select t1.gender, t1.day, sum(t2.score_points) as total
    from
    (
        select gender,day, sum(score_points) as score_points from Scores group by gender, day 
        order by gender, day
    )t1 
    join 
    (
        select gender,day, sum(score_points) as score_points from Scores group by gender, day 
        order by gender, day
    )t2 
    on t1.gender=t2.gender and t2.day<=t1.day 
    group by t1.gender, t1.day 
    order by t1.gender, t1.day