SQL题目及思路(16-20) 牛客网

题目：

16.统计出当前各个title类型对应的员工当前薪水对应的平均工资。结果给出title以及平均工资avg。

select title,avg(salary) as avg   //薪水对应的平均工资
from salaries,titles
where salaries.emp_no = titles.emp_no
and salaries.to_date='9999-01-01'  //筛选当前
and titles.to_date='9999-01-01'
group by titles.title;  //根据title类型进行分组

17.获取当前（to_date='9999-01-01'）薪水第二多的员工的emp_no以及其对应的薪水salary

select emp_no,salary
from salaries
where to_date='9999-01-01'
order by salary desc //根据salary进行排序，desc表示逆序
limit 1,1 //输出一个

18.查找当前薪水(to_date='9999-01-01')排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不准使用order by

由于题目要求不能用order by，因此我们采用下面的这种方式

select e.emp_no, max(s.salary), e.last_name, e.first_name
from employees as e, salaries as s
where s.to_date = '9999-01-01'
and e.emp_no = s.emp_no
and s.salary not in (
    select max(salary)
    from salaries
    where to_date = '9999-01-01'
)     //即筛选除了最多薪水的员工外，薪水最多的员工。

19.查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工

由于题目要求包括暂时没有分配部门的员工，因此我们采用左外连接

select a.last_name,a.first_name,b.dept_name
from employees a
left join dept_emp c
on a.emp_no=c.emp_no 
left join departments b
on b.dept_no=c.dept_no

20.查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth

先分别找到emp_no=10001的员工的第一次工资记录与最后一次工资记录，再将最后一次工资记录减去第一次工资记录得到入职以来salary的涨幅，用growth来表示。

SELECT ( 
    (
        SELECT salary FROM salaries 
     WHERE emp_no = 10001 
     ORDER BY to_date DESC 
     LIMIT 1
    )
    -
    (
        SELECT salary FROM salaries 
        WHERE emp_no = 10001 
        ORDER BY to_date ASC 
        LIMIT 1
    )
) AS growth