题目描述
首先这是一张原始表，包含公司id,员工id,员工名字，以及工资。
题目要求，将工资调整为税后工资。具体的税收标准为：
The tax rate is calculated for each company based on the following criteria:
0 If the max salary of any employee in the company is less than 1000 .
0.24 If the max salary of any employee in the company is in the range [1000, 10000] inclusive.
0.49 If the max salary of any employee in the company is greater than 10000.
Return the result table in any order. Round the salary to the nearest integer.
My Answer
大致思路就是先得到一张子表b用于记录每个公司的最高工资，然后将其与原始表合并，合并后的结果中每条数据对应的员工都有其对应公司的最高工资数据。然后将该最高工资作为case when的条件，对每个员工的工资进行税后调整，即??对应的税率。具体代码如下
select company_id,
employee_id,
employee_name,
round((case when max_salary<1000 then salary
when max_salary>=1000 and max_salary<=10000 then salary*(1-0.24)
else salary*(1-0.49) end),0) as salary
from
(select a.*,max_salary from Salaries a left join (select company_id,max(salary) max_salary from Salaries group by company_id) as b on a.company_id =b.company_id) as t;