【牛客网】SQL篇（SQL1~SQL17）

面试题1：查找最晚入职员工的所有信息

解题思路

• 最晚入职员工，很明显是要按照hire_date字段进行排序，并且逆序，取第一个（limit）

源代码

select * from employees order by hire_date desc limit 1;

面试题2：查找入职员工时间排名倒数第三的员工所有信息

解题思路

• 可以利用limit取到前三名，然后再从前三名中取出最后一名

源代码

• 解法1：

  select * from (select * from employees order by hire_date desc limit 3)tmp order by hire_date limit 1;
  

• 解法2：

  select * from employees order by hire_date desc limit 1 offset 2;
  

• 解法3：【开窗】

  select emp_no,birth_date,first_name,last_name,gender,hire_date
  from(
      select
      	*,row_number() over (order by hire_date desc) as num
  	from 
      	employees
  )tmp
  where num=3;
  

补充知识

• limit用法：
  • limit X：选取X个数据
  • limit X,Y：先跳过X个数据，选取Y个数据
• offset用法：
  • offset X：跳过X个数据

面试题3：查找当前薪水详情以及部门编号dept_no

解题思路

• join操作

源代码

# 注意两个表中重复的字段必须指定字段来源于哪个表中，否则sql会报错
select
    dm.emp_no,salary,from_date,dm.to_date,dept_no
from
    salaries
join dept_manager dm on salaries.emp_no = dm.emp_no

面试题4：查找所有已经分配部门的员工的last_name和first_name以及dept_no

解题思路

• 先关注要查询的字段，明显dept_no不再employees表中，那么只通过这一张表，肯定是不能完成查询的要求的，于是我们需要将带有dept_no字段的表进行join连接；已经分配部门也就是说dept_no不为空即可
• 优化：实际上去判断dept_no不为空，是多余的；因为本身在建立表的时候就制定了该字段不为空的约束条件

源代码

select
    first_name,last_name,de.dept_no
from employees
join dept_emp de on employees.emp_no = de.emp_no
# where de.dept_no is not null;

面试题5：查找所有员工的last_name和first_name以及对应部门编号dept_no

解题思路

• 这个题目和上一个题目的区别在于是否返回的所有员工，那么也就是说没有分配部门的员工也要返回他们的信息，因此我们可以使用employees left join dept_emp，left join表示向左看齐，左表的所有信息都会返回

源代码

select
    last_name,first_name,de.dept_no
from employees
left join dept_emp de on employees.emp_no = de.emp_no;

面试题7：查找薪水记录超过15次的员工号emp_no以及其对应的记录次数t

解题思路

• 读完题目，我们可以知道这个题目需要按照emp_no进行分组，然后count(*)求得每个emp_no对应的数目，这里值得关注的是查询条件不能用where而是要用having，因为后面需要跟聚合函数的结果

源代码

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

补充知识

• WHERE语句在GROUP BY语句之前；SQL会在分组之前计算WHERE语句。
• HAVING语句在GROUP BY语句之后；SQL会在分组之后计算HAVING语句。

面试题8：找出所有员工当前薪水salary情况

解题思路

• 注意的一点就是相同的薪水只显示一次distinct
• 其实distinct去重有一种替代的方法：group by，也是可以达到去重的效果

源代码

select
    distinct salary
from
    salaries
order by salary desc;

面试题10：获取所有非manager的员工emp_no

解题思路

• 首先确定需要关注的表是哪些，dept_manager和employees，非manager的员工就是在employees去除在dept_manager中出现过的员工。

源代码

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

面试题11：获取所有员工当前的manager

解题思路

• 只要我们区分好dept_emp和dept_manager的emp_no字段的不同含义就可以将这道题目解决，第一个表中该字段可以理解为员工编号，第二个表中该字段可以理解为经理编号。

源代码

select
    dept_emp.emp_no,dm.emp_no as manager
from dept_emp
join dept_manager dm on dept_emp.dept_no = dm.dept_no
where dept_emp.emp_no!=dm.emp_no

面试题12：获取每个部门中当前员工薪水最高的相关信息【*】

解题思路

• 首先确定需要用到哪几张表，显然是部门员工表和员工薪水表，靠emp_no进行join，然后按照部门号进行分组，求得部门中薪水最高的员工编号以及薪水，并且升序排列
• 按照上述的分析看似很简单，但是题目中要求输出emp_no，在分组的情况下，select中只能分组字段或者是聚合值

源代码

select
    dept_no,d1.emp_no,salary
from dept_emp d1
join salaries s1 on d1.emp_no = s1.emp_no
where salary in
      (
          select max(salary)
          from salaries
          join dept_emp de on salaries.emp_no = de.emp_no and de.dept_no=d1.dept_no
          group by de.dept_no
      )
order by dept_no

面试题15：查找employees表所有emp_no为奇数

解题思路

• 判断emp_no%2=1就行了并且last_name不为Mary，且按照hire_date逆序排列

源代码

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

面试题16：统计出当前各个title类型对应的员工当前薪水对应的平均工资

解题思路

• 首先需要关联两张表，才能拿到title类型和salary的关系

源代码

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

面试题17：获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

解题思路

• 解法1：limit+offset
• 解法2：limit X,Y

源代码

select
    emp_no,salary
from salaries
order by salary desc
limit 1,1;