21.查找所有员工自入职以来的薪水涨幅情况

select emp_no, (max(salary)-min(salary)) as growth from salaries where emp_no = (select emp_no from salaries where to_date = '9999-01-01') group by emp_no order by growth asc

要最后一条记录中的钱减入职的钱

没想明白，为什么现在的工资表不能直接拿salaries表加上where to_date=‘9999-01-01’就可以了。为何还要去连employees表？

请问一下，这里为什么一定要left join，inner join为什么不可以呢

没有考虑离职的人员

写了好久没考虑到降薪的情况，感谢解答很清晰易懂

对不起，是我不看题了，是在职员工

https://www.nowcoder.com/share/jump/1704352663117 欢迎看看我的题解。窗口函数就可以搞定了。

select emp_no , (max(salary)-min(salary)) as growth from employees inner join salaries on employees.emp_no = salaries.emp_no where salaries.to_date='9999-01-01' group by employees.emp_no 请问这样为什么不对？？？

e.hire_date=s.from_date 请问下这里相等是什么意思 这两个我就知道hire_date 入职时间 from_date 什么意思不知道 能告诉下吗

请问入职工资那里 为什么on后面的条件不能这么写呢 (select salary,s.emp_no from salaries s, employees e where s.from_date = e.hire_date)

想问一下中间那里为什么一定要写成inner join 换成join就不对了

可以用join啊

SELECT s1.emp_no,((select salary from salaries s2 where to_date = "9999-01-01" and s2.emp_no = s1.emp_no)-min(salary)) as growth from salaries s1 where s1.emp_no in (select emp_no from salaries where to_date="9999-01-01") group by emp_no order by growth ;

这样为什么不对啊？ select s2.emp_no,(s1.salary-s2.salary) as growth FROM (select e.emp_no,s.salary from employees as e inner join salaries as s on e.emp_no=s.emp_no where e.hire_date=s.from_date)s2 inner join (select s.emp_no,s.salary from salaries as s where s.to_date="9999-01-01")s1 on s1.emp_no=s2.emp_no

麻烦的惊人……

困难就这啊？select a.emp_no,MAX(salary)-MIN(salary) as growth from employees a inner join salaries b on a.emp_no=b.emp_no group by a.emp_no order by growth desc

刚刚没看时间条件，但是我现在还是想说一句，困难就这啊？select a.emp_no,MAX(salary)-MIN(salary) as growth from employees a inner join salaries b on a.emp_no=b.emp_no where a.emp_no in(select a.emp_no from employees a inner join salaries b on a.emp_no = b.emp_no where from_date <> '9999-01-01' and to_date = '9999-01-01') group by a.emp_no order by growth desc

为什么现在工资表那里的代码不需要hire_date=from_date这个条件呢？

我觉得这个有点缺陷to_date=不一定是9999-01-01,如果to_date一定是9999-01-01，那这题就不配困难好吧。万一有人离职了，但是依旧要算涨薪多少呢？可以帮我看看这个 select c2.emp_no, (c2.salary-c1.salary) growth from (select s1.emp_no, s1.salary from employees e1 join salaries s1 on e1.emp_no=s1.emp_no and e1.hire_date=s1.from_date) c1 join (select s2.emp_no, s2.salary,rank()over(partition by s2.emp_no order by to_date desc) as t from employees e2 join salaries s2 on e2.emp_no=s2.emp_no) c2 on c1.emp_no=c2.emp_no where t=1