题解 | #对所有员工的薪水按照salary降序进行1-N的排名#

sql小白，只熟悉普通的查询~
以下代码用基础的语句层层嵌套，通俗易懂
思路大约是单独构建一个表，把增加转换成正数，减少为负数
SELECT user_id,grade_num,
case type
when 'add' then grade_num
else grade_num(-1)
end as grow from grade_info
随后和另一张表连接，把这个表分组求和，并且排序
SELECT user_id,name,grade_sum from (
SELECT c.user_id,b.name,sum(c.grow) as grade_sum FROM
(SELECT user_id,grade_num,
case type
when 'add' then grade_num
else grade_num(-1)
end as grow from grade_info) c
INNER JOIN user b on c.user_id=b.id
GROUP BY user_id
order by grade_sum desc) d
再用一次having语句，选出自己生成的表中含有grade_sum最大的那个值
代码很长是因为又引用了一次自己构造的表
SELECT user_id,name,grade_sum from (
SELECT c.user_id,b.name,sum(c.grow) as grade_sum FROM
(SELECT user_id,grade_num,
case type
when 'add' then grade_num
else grade_num(-1)
end as grow from grade_info) c
INNER JOIN user b on c.user_id=b.id
GROUP BY user_id
order by grade_sum desc) d
HAVING grade_sum in (
SELECT max(grade_sum) from(
SELECT c.user_id,b.name,sum(c.grow) as grade_sum FROM
(SELECT user_id,grade_num, case type
when 'add' then grade_num
else grade_num(-1) end as grow
from grade_info) c
INNER JOIN user b
on c.user_id=b.id
GROUP BY user_id
order by grade_sum desc) e)