1468 计算税后工资：利用max （） over 和case when 组合使用打上标签

Create table If Not Exists Salaries (company_id int, employee_id int, employee_name varchar(13), salary int)
Truncate table Salaries
insert into Salaries (company_id, employee_id, employee_name, salary) values ('1', '1', 'Tony', '2000')
insert into Salaries (company_id, employee_id, employee_name, salary) values ('1', '2', 'Pronub', '21300')
insert into Salaries (company_id, employee_id, employee_name, salary) values ('1', '3', 'Tyrrox', '10800')
insert into Salaries (company_id, employee_id, employee_name, salary) values ('2', '1', 'Pam', '300')
insert into Salaries (company_id, employee_id, employee_name, salary) values ('2', '7', 'Bassem', '450')
insert into Salaries (company_id, employee_id, employee_name, salary) values ('2', '9', 'Hermione', '700')
insert into Salaries (company_id, employee_id, employee_name, salary) values ('3', '7', 'Bocaben', '100')
insert into Salaries (company_id, employee_id, employee_name, salary) values ('3', '2', 'Ognjen', '2200')
insert into Salaries (company_id, employee_id, employee_name, salary) values ('3', '13', 'Nyancat', '3300')
insert into Salaries (company_id, employee_id, employee_name, salary) values ('3', '15', 'Morninngcat', '7777')

select a.company_id,a.employee_id,a.employee_name,round(salary-salary*rating/100,0) as 'salary'
from 
#窗口函数max()  over () 和聚合函数 的不同之处在于每个组返回多行，而聚合函数每个组只返回一行
(select   company_id, employee_id , employee_name, salary,
#通过case when 来构建 这个比例
case when max(salary)  over(partition by company_id)>10000 then 49
     when max(salary) over(partition by company_id)>=1000 and max(salary) over(partition by company_id)<=10000 then 24
else 0
end as 'rating'

from  Salaries )a