牛客网SQL刷题复盘（1）

牛客网SQL刷题复盘

Q1 查找最晚入职员工的所有信息

数据：employees

关键：找到hire_date最大值

简单的order by与limit可以做到，但如果最晚入职的员工有两人及以上，那么难以查询出完整数据。因此，筛选出hire_date=max(hire_date)的数据为最佳做法，不过需要利用子查询操作，直接写hire_date=max(hire_date)报错。

select * from employees
where hire_date = 
(select max(hire_date) from employees)

Q2 查找员工入职时间排名倒数第三的员工所有信息

数据：employees

同Q1，在不确定同一入职时间有多少员工的情况下，也不能用limit。本题首先要先确定时间倒数第三是哪天，再使用where进行筛选。

select * from employees
where hire_date = (
    select distinct hire_date 
    from employees 
    order by hire_date desc
    limit 2,1)

select distinct对所有的入职日期去重，再进行降序排序，limit 2,1指从第2+1位数1位，即第三位。

Q3 查找当前薪水详情以及部门编号dept_no

数据：薪水表salaries，各个部门的领导表dept_manager

问：找各个部门当前领导的薪水详情以及其对应部门编号dept_no，输出结果以salaries.emp_no升序排序，并且请注意输出结果里面dept_no列是最后一列

本题给了两张表，salaries只有员工编号emp_no和薪水salary，dept_manager中有emp_no和部门编号dept_no，需要使用表连接。

关键：1.题干“dept_manager.dept_no列是最后一列”，所以要把dept_manager连接到salaries上

2.领导表dept_manager中员工编号emp_no与薪水表salaries的emp_no不完全对应，本题要以dept_manager.emp_no为准，因此不可使用左连接，否则会保留非领导员工编号

select s.*, d.dept_no 
from salaries s
join dept_manager d
on s.emp_no = d.emp_no
order by s.emp_no

Q4 查找所有已经分配部门的员工的last_name和first_name以及dept_no

数据：员工表employees和部门表dept_emp

问：查找所有已经分配部门的员工的last_name和first_name以及dept_no，未分配的部门的员工不显示

此题是单纯的表连接，内连接就可以使dept_no为null的不显示

select last_name, first_name, dept_no 
from dept_emp d
join employees e
on d.emp_no = e.emp_no

Q5 查找所有已经分配部门的员工的last_name和first_name以及dept_no(包括暂时没有分配)

同Q4，区别在于需要显示dept_no为null的员工，那么内连接不适用，要以employees.emp_no为准，使用左连接

select last_name, first_name, dept_no 
from employees e
left join dept_emp d
on e.emp_no = d.emp_no 

通过Q4和Q5可以熟悉内连接和左右连接的用法与区别，刚接触很容易搞反，个人的想法是无论left还是right，a join b都会形成先a后b排序的一张表，左连接虽然听起来像是“在左边连接表”，但实际上左右连接是在告诉sql我们要以哪一列为基准去输出连接表，left join则是以a.c=b.c中的a表c列为依据，显示a.c列全部数据，即使其他列有空值，right同理。而内连接为有空值就把整行删除。

Q6 查找薪水记录超过15条的员工号emp_no以及其对应的记录次数t

数据：salaries

查找记录次数使用count函数，先分组，再使用having筛选

select emp_no, count(emp_no) t 
from salaries
group by emp_no
having count(emp_no) > 15

Q7 所有员工当前薪水salary情况

数据：salaries

问：找出所有员工具体的薪水salary情况，对于相同的薪水只显示一次，并按照逆序显示

关键：“只显示一次“意味着使用select distinct

select distinct salary
from salaries
order by salary desc

Q8 获取所有非部门领导的员工emp_no

数据：员工表employees和部门领导表dept_manager

使用not in和子查询，先查询所有领导的emp_no，然后查询不在其中的员工编号即可

select emp_no 
from employees
where emp_no not in (
    select emp_no 
    from dept_manager
    )

Q9 获取所有员工当前的manager

数据：员工表dept_emp和部门经理表dept_manager

问：获取所有的员工和员工对应的经理，如果员工本身是经理的话则不显示

表连接，不显示经理只需使员工表里的编号不等于经理表的编号

select e.emp_no, m.emp_no manager
from dept_emp e
join dept_manager m
on e.dept_no = m.dept_no
where e.emp_no != m.emp_no

Q10 获取每个部门中当前员工薪水最高的相关信息

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

问：获取每个部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号dept_no升序排列

（1）如果不需要给出emp_no字段

select d.dept_no, max(s.salary)
from dept_emp d
join salaries s
on d.emp_no = s.emp_no
group by d.dept_no

（2）原题

不能直接在select处添加emp_no，一个聚合字段(dept_no)对应多个非聚合字段(emp_no)，会随机选择非聚合字段中的任何一个。使用窗口函数对每组内薪水进行排名，再查询排名第一的员工信息即可。

select dept_no, emp_no, salary
from (
    select d.dept_no, d.emp_no, s.salary, dense_rank()over(partition by d.dept_no order by s.salary desc) rk
    from dept_emp d
    join salaries s
    on d.emp_no = s.emp_no
    ) f
where f.rk = 1