SQL-21 查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序...

「爱情、让人受尽委屈。」 2022-01-07 00:41 123阅读 0赞

## 题目描述 ##

查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp\_no以及其对应的薪水涨幅growth，并按照growth进行升序  
CREATE TABLE \`employees\` (  
\`emp\_no\` int(11) NOT NULL,  
\`birth\_date\` date NOT NULL,  
\`first\_name\` varchar(14) NOT NULL,  
\`last\_name\` varchar(16) NOT NULL,  
\`gender\` char(1) NOT NULL,  
\`hire\_date\` date NOT NULL,  
PRIMARY KEY (\`emp\_no\`));  
CREATE TABLE \`salaries\` (  
\`emp\_no\` int(11) NOT NULL,  
\`salary\` int(11) NOT NULL,  
\`from\_date\` date NOT NULL,  
\`to\_date\` date NOT NULL,  
PRIMARY KEY (\`emp\_no\`,\`from\_date\`));

## 输入描述: ##

无

## 输出描述: ##

<table> 
 <tbody> 
  <tr> 
   <th>emp_no</th> 
   <th>growth</th> 
  </tr> 
 </tbody> 
 <tbody> 
  <tr> 
   <td>10011</td> 
   <td>0</td> 
  </tr> 
  <tr> 
   <td>省略</td> 
   <td>省略</td> 
  </tr> 
  <tr> 
   <td>10010</td> 
   <td>54496</td> 
  </tr> 
  <tr> 
   <td>10004</td> 
   <td>34003</td> 
  </tr> 
 </tbody> 
</table>

SQL:

select sCurrent.emp_no,(sCurrent.salary-sStart.salary)as growth
    from(select emp_no,salary from salaries where to_date='9999-01-01')as sCurrent,
        (select e.emp_no,c.salary from employees e,salaries c where e.emp_no=c.emp_no and c.from_date=e.hire_date)as sStart
    where sCurrent.emp_no=sStart.emp_no
    order by growth

分别找出 当前的工资 以及入职时工资 将这两张表进行连接 然后计算出growth

转载于:https://www.cnblogs.com/kexiblog/p/10683481.html