with grade_add as
(
select user_id,sum(grade_num) as ad
from grade_info
where type='add'
group by user_id
),
grade_reduce as
(
select user_id,sum(grade_num) as re
from grade_info
where type='reduce'
group by user_id
),
grade as
(
select e.*,DENSE_RANK() over(order by e.grade_sum desc) as ranking
from (select a.id,a.name,
case when b.ad is not null and c.re is null then b.ad
when b.ad is not null and c.re is not null then (b.ad-c.re)
else 0 end as grade_sum
from user a
left join grade_add b
on a.id=b.user_id
left join grade_reduce c
on a.id=c.user_id) e
)
select id,name,grade_sum
from grade
where ranking=1
order by id;
笨办法，分别建立临时表加的积分和，减的积分和，以及最后相加减后最后的积分总数结果
然后排序挑选排名第一的数据