select c.id, c.name, c.grade_num
from
(select b.id id, b.name name, b.grade_num grade_num ,dense_rank()over(order by b.grade_num desc) d
from
(select a.id id, a.name name, sum(a.grade_num) grade_num
from
(select g.user_id id, u.name name, (case when g.type='add' then g.grade_num when g.type='reduce' then -g.grade_num end) grade_num
from user u right join grade_info g on u.id=g.user_id
) a #a表为把type='reduce'的grade_num变为负数
group by a.id, a.name
) b #b表为最终的积分值
) c #c表为对积分值的排序结果
where c.d=1
order by id asc
科华数据工作强度 62人发布