leetcode1468计算税后工资--if的嵌套使用和round配合使用

1468计算税后工资
题目分析：题目给了限定条件，不同情况下的工资是不同的，现在要求算出给定的表的税后的工资是多少。我们可以先给不同级别的公司定好对应的级别，然后按照级别进行分类计算就好了。
首先定好级别，使用if进行嵌套判断

select company_id,if(max(salary)<1000,"L",(if(max(salary)>10000,"H","M"))) type from Salaries group by company_id;

接着和salaries表进行inner join

select S.company_id,S.employee_id,S.employee_name,S.salary,T.type from Salaries S inner join (select company_id,if(max(salary)<1000,"L",(if(max(salary)>10000,"H","M"))) type from Salaries group by company_id) T on S.company_id = T.company_id

最后按照不同的级别计算工资（这个地方可以简化，就是第一步的定好级别直接使用数字进行判断就行了）

select company_id,employee_id,employee_name,round(if(type="L",salary,if(type="H",salary*0.51,salary*0.76)),0) salary from (
select S.company_id,S.employee_id,S.employee_name,S.salary,T.type from Salaries S inner join (select company_id,if(max(salary)<1000,"L",(if(max(salary)>10000,"H","M"))) type from Salaries group by company_id) T on S.company_id = T.company_id) F

提交结果如下：