27.【较难】给出每个员工每年薪水涨幅超过5000的员工编号、薪水变更开始日期以及薪水涨幅值

法一

select s1.emp_no,
       s1.from_date,
       s1.salary-s2.salary as salary_growth
from salaries s1,salaries s2
where s1.emp_no=s2.emp_no
and (strftime('%Y', s1.to_date)-strftime('%Y', s2.to_date)=1 or
    strftime('%Y', s1.from_date)-strftime('%Y', s2.from_date)=1)
and s1.salary-s2.salary>5000
order by salary_growth desc

法二

select s1.emp_no,
       s1.from_date,
       s1.salary-s2.salary as salary_growth
from salaries s1,salaries s2
where s1.emp_no=s2.emp_no
and  s1.from_date=s2.to_date
-- 保证S1表是S2后面的表，例如S1.from_date为'1987-06-26'，S2.from_date为'1986-06-26'
and s1.salary-s2.salary>5000
order by salary_growth desc