牛客网sql实战（二）

23.对所有员工的当前(to_date='9999-01-01')薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列
法一：

select a.emp_no,a.salary,
(select count(distinct b.salary) from salaries as b where b.salary>=a.salary
and b.to_date='9999-01-01') as rank
from salaries as a where a.to_date='9999-01-01' 
order by a.salary desc,emp_no asc;

法二：

select s1.emp_no,s1.salary,count(distinct s2.salary) as rank
from salaries s1,salaries s2
where s1.to_date='9999-01-01' and s2.to_date='9999-01-01'
and s1.salary<=s2.salary
group by s1.emp_no
order by s1.salary desc ,s1.emp_no asc

24.获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date='9999-01-01'
无脑合并表

select a.dept_no,b.emp_no,c.salary 
from dept_emp as a,employees as b,salaries as c
where a.emp_no=b.emp_no 
and b.emp_no=c.emp_no
and a.to_date='9999-01-01'
and c.to_date='9999-01-01'
and b.emp_no not in (select emp_no from dept_manager where to_date='9999-01-01')

25.获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date='9999-01-01',
结果第一列给出员工的emp_no，
第二列给出其manager的manager_no，
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary

• 获取所有员工的当前薪水情况
• 获取所有管理者的当前薪水情况
• 找出员工大于管理者薪水的情况

select c.emp_no,d.emp_no as manager_no,c.salary,d.salary from 
(select a.emp_no,a.dept_no,b.salary from dept_emp as a inner join salaries as b on a.emp_no=b.emp_no
where a.to_date='9999-01-01' and b.to_date='9999-01-01') as c,
(select a.emp_no,a.dept_no,b.salary from dept_manager as a inner join salaries as b on a.emp_no=b.emp_no
where a.to_date='9999-01-01' and b.to_date='9999-01-01') as d
where c.dept_no=d.dept_no and c.salary>d.salary

26.汇总各个部门当前员工的title类型的分配数目，结果给出部门编号dept_no、dept_name、其当前员工所有的title以及该类型title对应的数目count
有点复杂

select b.dept_no,b.dept_name,c.title,
(count(c.title)) as count
from dept_emp a 
inner join departments b on a.dept_no=b.dept_no 
inner join titles c on a.emp_no=c.emp_no
where a.to_date='9999-01-01' and c.to_date='9999-01-01'
group by b.dept_no,c.title;

27.给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth，并按照salary_growth逆序排列。
提示：在sqlite中获取datetime时间对应的年份函数为strftime('%Y', to_date)
本题结题关键：每年的薪水涨幅

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