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

与第二题类似，主要区别是需要额外判断sum(grade_num)中的grade_num是加还是减。

可以通过(case when ...) 或 if(...)条件分支判断求和

这里依旧给出两种方案来进行解答。

方案一：

select u.id, u.name, sum(case when type='add' then grade_num else grade_num * -1 end) as grade_sum
from user u
join grade_info g
on u.id = g.user_id
group by u.id, u.name
having grade_sum = (select sum(case when type='add' then grade_num else grade_num * -1 end) as grade_sum
										from grade_info g1
										group by user_id
										order by grade_sum desc
										limit 0, 1)
order by u.id

方案二：with ... as ... 构建临时表

with t as (
	select user_id, sum(case when type='add' then grade_num else grade_num*-1 end) as grade_sum
	from grade_info
	group by user_id
)
select id, name, grade_sum
from user u
join t
on u.id = t.user_id
where grade_sum = (select max(grade_sum) from t)
order by id