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

新建表temp1，根据user_id，type分组汇总并求出每组积分和。

with temp1 as 
(select user_id, type, sum(grade_num) as sub_sum from grade_info
group by user_id, type),

新建表temp2，连接user和grade_info并选出所需字段。因为积分分为add和reduce两种，所以算实际积分需要用type=add的积分总和减去type=reduce的积分总和。我选择的方法是用if判断当前user_id在temp1中是否有对应的type=add，如果有就用对应的积分总和，如果没有则为0。用相同的办法得到type=reduce的积分总和。然后入上述两者相减得到实际积分。

temp2 as
(select distinct u.id, u.name, 
(if(exists(select sub_sum from temp1 where user_id=u.id and type="add"),
   (select sub_sum from temp1 where user_id=u.id and type="add"),
   0)-
if(exists(select sub_sum from temp1 where user_id=u.id and type="reduce"),
   (select sub_sum from temp1 where user_id=u.id and type="reduce"),
   0)) as diff
from user as u, grade_info as g)

最后从temp2选出全部字段，并用子查询从temp2中找到实际积分diff的最大值。然后通过where限制diff等于其最大值。

完整答案如下。

with temp1 as 
(select user_id, type, sum(grade_num) as sub_sum from grade_info
group by user_id, type),

temp2 as
(select distinct u.id, u.name, 
(if(exists(select sub_sum from temp1 where user_id=u.id and type="add"),
   (select sub_sum from temp1 where user_id=u.id and type="add"),
   0)-
if(exists(select sub_sum from temp1 where user_id=u.id and type="reduce"),
   (select sub_sum from temp1 where user_id=u.id and type="reduce"),
   0)) as diff
from user as u, grade_info as g)

select * from temp2
where diff = (select max(diff) from temp2)
order by id