解法一
1、创建临时表,用case when 函数将tpye='reduce' 的grade_num 改为负值
with tmp_table as (select *,case grade_num when type='reduce' then -grade_num else grade_num end as grade_num1 from grade_info)2、沿用上一题的解法一 将grade_info 替换为temp_table
with tmp_table as (select *,case grade_num when type='reduce' then -grade_num else grade_num end as grade_num1 from grade_info) select a.id,a.name,sum(b.grade_num1) as grade_sum from user as a join tmp_table as b on a.id=b.user_id group by a.id,a.name having grade_sum= (select max(grade_sum) from (select user_id,sum(grade_num1) as grade_sum from tmp_table group by user_id) as c) order by id;
解法二 临时1表+窗口函数(沿用上一题的解法二)
with tmp_table as (select *,case grade_num when type='reduce' then -grade_num else grade_num end as grade_num1 from grade_info) select b.id,b.name,a.grade_sum from (select user_id,sum(grade_num1) as grade_sum, rank() over(order by sum(grade_num1) desc ) as rnk from tmp_table group by user_id) as a join user as b on a.user_id=b.id where a.rnk=1 order by b.id;
解法三 if函数+窗口函数 最简单的写法
select b.id,b.name,a.grade_sum from (select user_id,sum(if(type='reduce',-grade_num,grade_num)) as grade_sum, rank() over(order by sum(if (type='reduce',-grade_num,grade_num)) desc ) as rnk from grade_info group by user_id) as a join user as b on a.user_id=b.id where a.rnk=1 order by b.id;