牛客网SQL刷题复盘（2）

Q11 查找employees表emp_no与last_name的员工信息

数据：员工表employees

问：查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列

思路：如何查找emp_no为奇数的员工。通常使用2n+1来表示奇数，因此筛选emp_no除以2余数为1的员工即可。

select * from employees
where last_name != 'Mary'
and emp_no % 2 = 1
order by hire_date desc

Q12 统计出各个title类型员工薪水对应的平均工资

数据：员工职称表titles和薪水表salaries

问：统计出各个title类型对应的员工薪水对应的平均工资avg，结果给出title以及平均工资avg，并且以avg升序排序

思路：首先进行表连接操作把title与salary进行对应，再分组求平均。类似Q10(1)

select title, avg(salary)
from titles t
join salaries s
on t.emp_no = s.emp_no
group by title
order by avg(salary)

Q13 获取薪水第二多的员工的emp_no以及其对应的薪水salary

数据：薪水表salaries

思路：先用窗口函数对薪水排序，再查询rk为2即可。

select emp_no, salary from (
    select emp_no, salary, dense_rank()over(order by salary) rk 
    from salaries
    ) f 
where f.rk = 2
order by emp_no

Q14 获取薪水第二多的员工信息（不使用order by）

数据：员工表employees和薪水表salaries

问：查找薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不能使用order by完成

思路：首先查找出薪水最多select max(salary) from salaries，将其排除在外where salary<(select max(salary) from salaries)，再查找最大。

select s.emp_no, salary, last_name, first_name
from salaries s 
join employees e
on s.emp_no = e.emp_no
where salary = (
    select max(salary)
    from salaries
    where salary < (
        select max(salary)
        from salaries
        )
)

Q15 查找所有员工的last_name和first_name以及对应的dept_name

数据：员工表employees、部门表departments、部门员工关系表dept_emp

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

思路：两次表连接

select last_name, first_name, dept_name
from employees e 
left join dept_emp dp on e.emp_no = dp.emp_no
left join departments ds on dp.dept_no = ds.dept_no

Q16 查找在职员工自入职以来的薪水涨幅情况

数据：员工表employees和薪水表salaries

问：查找在职员工自入职以来的薪水涨幅情况，给出在职员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序

思路：首先要从原表中把入职薪水和当前薪水筛选出来，将两表连接令hire_date = from_date得到员工对应入职薪水，再连接并将条件设置为to_date='9999-01-01'可得到当前薪水。

select f1.emp_no, (f2.salary-f1.salary) growth from 
(select e.emp_no, salary from employees e
left join salaries s
on e.hire_date = s.from_date
and e.emp_no = s.emp_no) f1
join 
(select e.emp_no, salary from employees e 
left join salaries s
on e.emp_no= s.emp_no
where s.to_date='9999-01-01') f2
on f1.emp_no=f2.emp_no
order by growth

Q17 统计各个部门的工资记录数

数据：部门表departments、部门员工关系表dept_emp、薪水表salaries

问：统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum，按照dept_no升序排序

思路：把三个表连接到一起，再使用分组去count每个组内的工资记录数。

select dp.dept_no, dept_name, count(salary) sum
from salaries s
left join dept_emp dp on s.emp_no = dp.emp_no
left join departments ds on dp.dept_no = ds.dept_no
group by dp.dept_no
order by dp.dept_no

Q18 对所有员工的薪水按照salary降序进行1-N的排名

数据：薪水表salaries

问：对所有员工的薪水按照salary降序先进行1-N的排名，如果salary相同，再按照emp_no升序排列

思路：比较简单，使用窗口函数即可，最后先对排名order by，再对emp_no排。

select emp_no, salary, dense_rank()over(order by salary desc) t_rank
from salaries
order by t_rank, emp_no

Q19 获取所有非manager员工当前的薪水情况

数据：员工表employees、部门员工关系表dept_emp、部门经理表dept_manager、薪水表salaries

问：获取所有非manager员工薪水情况，给出dept_no、emp_no以及salary

思路：把除了经理表的三个表连接，再not in经理表中的员工编号（做法很多，可以在网站上选择适合自己的）

select d.dept_no, e.emp_no, salary
from employees e
join dept_emp d on e.emp_no = d.emp_no 
join salaries s  on e.emp_no = s.emp_no
where e.emp_no not in (select emp_no from dept_manager)

Q20 获取员工其当前的薪水比其manager当前薪水还高的相关信息

数据：部门关系表dept_emp、部门经理表dept_manager、薪水表salaries

问：获取员工其当前的薪水比其manager当前薪水还高的相关信息，第一列给出员工的emp_no，第二列给出其manager的manager_no，第三列给出该员工当前的薪水emp_salary，第四列给该员工对应的manager当前的薪水manager_salary

思路：构建员工工资表与经理工资表，再连接使emp_salary>manager_salary

select f1.emp_no, f2.manager_no, f1.emp_salary, f2.manager_salary
from(
    select de.dept_no, de.emp_no, salary emp_salary
    from dept_emp de
    join salaries s
    on de.emp_no = s.emp_no
    ) f1
join(
    select dept_no, dm.emp_no manager_no, salary manager_salary 
    from salaries s
    join dept_manager dm
    on s.emp_no = dm.emp_no 
    ) f2
on f1.dept_no = f2.dept_no
where f1.emp_salary > f2.manager_salary