Medium之1468.计算税后工资

ゝ一世哀愁。 2022-08-31 11:23 167阅读 0赞

**Salaries 表：**

<table> 
 <thead> 
  <tr> 
   <th>Column Name</th> 
   <th>Type</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>company_id</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>employee_id</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>employee_name</td> 
   <td>varchar</td> 
  </tr> 
  <tr> 
   <td>salary</td> 
   <td>int</td> 
  </tr> 
 </tbody> 
</table>

(company\_id, employee\_id) 是这个表的主键  
这个表包括员工的company id, id, name 和 salary

### 问题 ###

写一条查询 SQL 来查找每个员工的税后工资

每个公司的税率计算依照以下规则

如果这个公司员工**最高**工资不到 1000 ，税率为 0%  
如果这个公司员工**最高**工资在 1000 到 10000 之间，税率为 24%  
如果这个公司员工**最高**工资大于 10000 ，税率为 49%  
按任意顺序返回结果，税后工资结果取整

### 示例 ###

**Salaries 表：**

<table> 
 <thead> 
  <tr> 
   <th>company_id</th> 
   <th>employee_id</th> 
   <th>employee_name</th> 
   <th>salary</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>1</td> 
   <td>Tony</td> 
   <td>2000</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>2</td> 
   <td>Pronub</td> 
   <td>21300</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>3</td> 
   <td>Tyrrox</td> 
   <td>10800</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>1</td> 
   <td>Pam</td> 
   <td>300</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>7</td> 
   <td>Bassem</td> 
   <td>450</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>9</td> 
   <td>Hermione</td> 
   <td>700</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>7</td> 
   <td>Bocaben</td> 
   <td>100</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>2</td> 
   <td>Ognjen</td> 
   <td>2200</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>13</td> 
   <td>Nyancat</td> 
   <td>3300</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>15</td> 
   <td>Morninngcat</td> 
   <td>7777</td> 
  </tr> 
 </tbody> 
</table>

**Result 表：**

<table> 
 <thead> 
  <tr> 
   <th>company_id</th> 
   <th>employee_id</th> 
   <th>employee_name</th> 
   <th>salary</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>1</td> 
   <td>Tony</td> 
   <td>1020</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>2</td> 
   <td>Pronub</td> 
   <td>10863</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>3</td> 
   <td>Tyrrox</td> 
   <td>5508</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>1</td> 
   <td>Pam</td> 
   <td>300</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>7</td> 
   <td>Bassem</td> 
   <td>450</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>9</td> 
   <td>Hermione</td> 
   <td>700</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>7</td> 
   <td>Bocaben</td> 
   <td>76</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>2</td> 
   <td>Ognjen</td> 
   <td>1672</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>13</td> 
   <td>Nyancat</td> 
   <td>2508</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>15</td> 
   <td>Morninngcat</td> 
   <td>5911</td> 
  </tr> 
 </tbody> 
</table>

对于公司 1 ，最高工资是 21300 ，其每个员工的税率为 49%  
对于公司 2 ，最高工资是 700 ，其每个员工税率为 0%  
对于公司 3 ，最高工资是 7777 ，其每个员工税率是 24%  
税后工资计算 = 工资 - ( 税率 / 100）\*工资  
对于上述案例，Morninngcat 的税后工资 = 7777 - 7777 \* ( 24 / 100) = 7777 - 1866.48 = 5910.52 ，取整为 5911

来源：力扣（LeetCode）  
链接：https://leetcode-cn.com/problems/calculate-salaries  
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

### 解答 ###

mysql不认识百分比计算，得化为小数。  
取整数，不是Int,可以round(&,0)

##### 法一：自联结 #####

select s.company_id,s.employee_id,employee_name,
            round(case when max_sal<=1000 then salary
                       when max_sal>1000 and max_sal<=10000 then 0.76*salary
                       else 0.51*salary
                  end,0) as salary
    from salaries s JOIN 
        (select company_id,max(salary) max_sal
         from salaries
         group by company_id) t
    on s.company_id = t.company_id

注意，这里不能直接  
from (select company\_id,max(salary) max\_sal  
from salaries  
group by company\_id)  
这样只会有count(distinct company\_id)条记录，所以一定要通过原表和这个临时表 内连接。  
也可以from （select \*,max(salary) over(partition by company\_id max\_sal from salaries)

##### 法二：窗口函数 #####

select company_id,employee_id,employee_name,
            round(case when max(salary) over(partition by company_id)<1000 then salary
                       when max(salary) over(partition by company_id) between 1000 and 10000 then 0.76*salary
                       else 0.51*salary
                  end,0) as salary
    from salaries