algorithm

牛客题霸—SQL篇

宋正兵 更新 on 2021-05-07

SQL数据库实战题_面试必刷+解析_牛客题霸_牛客网 (nowcoder.com)

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

1
2
3
4
# 方法1：使用子查询
SELECT * FROM employees WHERE hire_date=(SELECT MAX(hire_date) FROM employees);
# 方法2：使用 order by 排序 和 limit 限制一个
SELECT * FROM employees ORDER BY hire_date DESC limit 0, 1;

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

可能有入职时间相同的员工，所以利用 SELECT DISTINCT 语句，仅仅列出不同（distinct）的值。

1
2
SELECT * FROM employees 
WHERE hire_date=(SELECT DISTINCT hire_date FROM employees ORDER BY hire_date DESC LIMIT 1 OFFSET 2);

SQL3 查找各个部门当前领导当前薪水详情以及其对应部门编号dept_no

WHERE 的两个条件是确保还在职，通过 SELECT s.*, d.dept_no 提取出我们需要的列。

1
2
3
4
SELECT s.*, d.dept_no FROM salaries s INNER JOIN dept_manager d 
ON s.emp_no=d.emp_no 
WHERE s.to_date = '9999-01-01' and d.to_date='9999-01-01' 
ORDER BY s.emp_no;

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

用内连接

1
2
SELECT e.last_name,e.first_name,d.dept_no FROM employees e 
INNER JOIN dept_emp d ON e.emp_no = d.emp_no;

SQL5 查找所有员工的last_name和first_name以及对应部门编号dept_no

观察输出要求，很明显我们要用到的是左外连接，输出全部左表内容，右表没有的用 null 替代。

1
2
3
SELECT e.last_name, e.first_name,d.dept_no 
FROM employees e LEFT OUTER JOIN dept_emp d 
ON e.emp_no=d.emp_no;

SQL7 查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t

1、用 COUNT() 函数和 GROUP BY 语句可以统计同一 emp_no 值的记录条数

2、根据题意，输出的变动次数为 t，故用 AS 语句将 COUNT(emp_no) 的值转换为 t

3、由于 COUNT() 函数不可用于 WHERE 语句中，故使用 HAVING 语句来限定 t>15 的条件

1
SELECT emp_no, COUNT(emp_no) AS t FROM salaries GROUP BY emp_no HAVING COUNT(emp_no) > 15;

SQL8 找出所有员工当前具体的薪水salary情况

对于 DISTINCT 与 GROUP BY 的使用：

  1. 当对系统的性能高并且数据量大时使用 GROUP BY
  2. 当对系统的性能不高时或者使用数据量少时两者皆可
  3. 尽量使用 GROUP BY
1
2
3
4
# 方法1：DISTINCT
SELECT DISTINCT salary FROM salaries WHERE to_date='9999-01-01' ORDER BY salary DESC;
# 方法2：GROUP BY
SELECT salary FROM salaries WHERE to_date='9999-01-01' GROUP BY salary ORDER BY salary DESC;

SQL10 获取所有非manager的员工emp_no

IN 关键字适合确定数量的情况，一般效率较低。能用 IN 关键字的语句都可以转化为使用 JOIN 的语句，推荐使用 JOIN。

1
2
3
4
5
# 方法一：使用NOT IN选出在employees但不在dept_manager中的emp_no记录
SELECT emp_no FROM employees WHERE emp_no NOT IN (SELECT emp_no FROM dept_manager);
# 方法二：先使用LEFT JOIN连接两张表，再从此表中选出dept_no值为NULL对应的emp_no记录
SELECT e.emp_no FROM employees e LEFT OUTER JOIN dept_manager d
ON e.emp_no=d.emp_no WHERE d.dept_no IS NULL;

SQL11 获取所有员工当前的manager

1、用 INNER JOIN 连接两张表，因为要输出自己的经理，得知自己与经理的部门要相同，故有限制条件 de.dept_no = dm.dept_no

2、再用 WHERE 限制当前员工与当前经理的条件，即 dm.to_date='9999-01-01'、de.to_date='9999-01-01' 、de.emp_no<>dm.emp_no

3、为了增强代码可读性，将 dept_emp 用别名 de 代替，dept_manager 用 dm 代替，最后根据题意将 de.emp_no 用别名 manager_no 代替后输出

1
2
3
SELECT de.emp_no, dm.emp_no manager FROM dept_emp de INNER JOIN dept_manager dm 
ON de.dept_no=dm.dept_no 
WHERE dm.to_date='9999-01-01' AND de.to_date='9999-01-01' AND de.emp_no<>dm.emp_no;

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

这个题挺难的，理解不了就跳过吧，别浪费时间。

思路：在子表中查找部门编号 dept_no 等于主表中部门编号 dept_no 的最大薪水，然后主表的 WHERE 条件限制

1
2
3
4
5
6
7
SELECT d.dept_no, d.emp_no, s.salary maxSalary 
FROM dept_emp d INNER JOIN salaries s
ON d.emp_no=s.emp_no
# 部门相同且薪水等于该部门的最大薪水
WHERE salary=(SELECT MAX(salary) FROM dept_emp d2 INNER JOIN salaries s2
              ON d2.emp_no=s2.emp_no WHERE d2.dept_no=d.dept_no)
ORDER BY dept_no;

SQL15 查找employees表

emp_no 为奇数，用位运算可能会快一些。

1
2
3
SELECT * FROM employees 
WHERE emp_no&1=1 AND last_name<>'Mary'
ORDER BY hire_date DESC;

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

AVG(*) 是自动命名为 avg 的，所以不用重命名

1
2
3
SELECT t.title, AVG(s.salary) FROM titles t INNER JOIN salaries s
ON t.emp_no=s.emp_no AND t.to_date='9999-01-01' AND s.to_date='9999-01-01' 
GROUP BY t.title;

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

同一薪水可能有多人，所以需要用 GROUP BY 来对 salary 分组或者 DISTINCT 来对 salary 去重，得到第二高的薪水，然后主表再用 WHERE 进行查询。

1
2
3
4
5
SELECT emp_no, salary FROM salaries 
WHERE salary=(
    SELECT salary FROM salaries GROUP BY salary ORDER BY salary DESC LIMIT 1 OFFSET 1
)
AND to_date='9999-01-01';

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

题目要求不能使用 ORDER BY，那么只能曲线救国了，先查出原表的最高工资，然后查出了原表的最高工资以外的最高工资（即第二高工资），然后根据该条件去查出薪水第二多的员工信息。

1
2
3
4
5
6
7
8
9
10
11
SELECT s.emp_no,s.salary,e.last_name,e.first_name
FROM employees e INNER JOIN salaries s ON s.emp_no=e.emp_no
# 第三步: 将第二高工资作为查询条件
WHERE salary=(
    # 第二步: 查出除了原表最高工资以外的最高工资(第二高工资)
    SELECT MAX(salary) FROM salaries
    WHERE salary<(
        # 第一步: 查出原表最高工资
        SELECT MAX(salary) FROM salaries WHERE to_date='9999-01-01'
    ) AND to_date='9999-01-01'
) AND s.to_date='9999-01-01'

方法二，也是模板方法 评论区里的答案详解_牛客博客 (nowcoder.net)

因为是按照 s1.salary <= s2.salary 来连接的，按照 s1.salary 分组后，一个 s1.salary 对应 1 个或多个大于等于它的 s2.salary。假设没有重复，如果 s1.salary=MAX(salary) 那么它就只对应一个 s2.salary，即最大的那个值。如果 s1.salary 是第二大的，那么它对应的就有 2 个 s2.salary，即最大的和第二大的。于是我们可以利用 HAVING COUNT(s2.salary)=n 来找到第 n 大的 salary。那么在有重复的情况下，我们只需要加上 DISTINCT 来对每一组数据进行去重就满足条件了。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select s.emp_no, s.salary, e.last_name, e.first_name
from salaries s join employees e
on s.emp_no = e.emp_no
where s.salary =
    (
    select s1.salary
    from salaries s1 join salaries s2      -- 自连接查询
    on s1.salary <= s2.salary
    group by s1.salary                     -- 当s1<=s2链接并以s1.salary分组时一个s1会对应多个s2
    having count(distinct s2.salary) = 2   -- (去重之后的数量就是对应的名次)
    and s1.to_date = '9999-01-01'
    and s2.to_date = '9999-01-01'
    )
and s.to_date = '9999-01-01'

表自连接以后:

s1 s2
100 100
98 98
98 98
95 95

当s1<=s2链接并以s1.salary分组时一个s1会对应多个s2

s1 s2
100 100
98 100
98
98
95 100
98
98
95

对s2进行去重统计数量, 就是s1对应的排名

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

先内连接 departments 表和 dept_emp 表形成新表 t，再左连接 employees 表和 t 表

1
2
3
4
SELECT e.last_name, e.first_name, t.dept_name FROM employees e LEFT JOIN (
    SELECT d.dept_no,d.dept_name,de.emp_no FROM departments d INNER JOIN dept_emp de ON d.dept_no=de.dept_no
    WHERE de.to_date='9999-01-01'
) t ON e.emp_no=t.emp_no;

先左连接 employees 表和 dept_emp表，根据 emp_no 来确定每个员工的部门 dept_no，然后再左连接 departments 表，根据 dept_no 来确定所对应的的部门名称。

1
2
3
4
SELECT last_name, first_name, dept_name
FROM employees
LEFT JOIN dept_emp ON employees.emp_no=dept_emp.emp_no
LEFT JOIN departments ON dept_emp.dept_no=departments.dept_no;

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

1
2
3
4
5
6
7
8
9
10
SELECT s1.emp_no, s2.salary-s1.salary AS growth 
FROM (
    SELECT e.emp_no, s.salary FROM employees e INNER JOIN salaries s 
    ON e.emp_no=s.emp_no WHERE e.hire_date=s.from_date
) AS s1  -- 入职薪水
INNER JOIN (
    SELECT emp_no, salary FROM salaries WHERE to_date='9999-01-01'
) AS s2  -- 现在薪水
ON s1.emp_no=s2.emp_no  
ORDER BY growth

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

三表联查

1
2
3
4
SELECT d.dept_no, d.dept_name, COUNT(*) AS sum
FROM departments AS d, dept_emp AS de, salaries AS s
WHERE d.dept_no=de.dept_no AND de.emp_no=s.emp_no
GROUP BY d.dept_no ORDER BY d.dept_no;

SQL23 对所有员工的当前薪水按照salary进行按照1-N的排名

SQL窗口函数。通俗易懂的学会：SQL窗口函数 - 知乎 (zhihu.com)

1
2
3
SELECT emp_no, salary, dense_rank() OVER (ORDER BY salary DESC) AS t_rank FROM salaries
WHERE to_date='9999-01-01'
ORDER BY t_rank;

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

先去 dept_manager 表中找到所有经理的 emp_no，然后内连接 employees 表和 dept_emp 表查询不是经理的所有员工，令结果为新表 t1。

然后把新表 t1 和 salaries 表内连接查询结果。

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT 
    t1.dept_no,
    t1.emp_no,
    s.salary
FROM salaries s
INNER JOIN (
    SELECT de.emp_no, de.dept_no
    FROM employees e INNER JOIN dept_emp de ON e.emp_no = de.emp_no
    WHERE de.emp_no NOT IN (
        SELECT emp_no FROM dept_manager WHERE to_date='9999-01-01'
    )
) t1 
ON s.emp_no = t1.emp_no;



标签
algorithm