[牛客数据库SQL实战] 1~10题及个人解答

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

-- 先进行逆序排序，然后只输出第一个
-- 对hire_date字段排序降序，此时最晚的时间排在第一个，再用LIMIT取出
SELECT * FROM employees ORDER BY hire_date DESC LIMIT 1;

运行时间：16ms

占用内存：3320k

-- 最晚入职员工可能不只一个，先选出最晚入职时间，再对应到编号，由编号识别出最晚入职员工
-- 先找出 hire_date 字段的最大值，再把该值当成 employees 表的 hire_date 查询条件
SELECT MAX(hire_date) FROM employees;
SELECT * FROM employees WHERE hire_date = (SELECT MAX(hire_date) FROM employees);

运行时间：17ms

占用内存：3320k

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

-- 入职时间hire_date 排名倒数order by ? desc 第三limit 2,1
SELECT * FROM employees ORDER BY hire_date DESC LIMIT 2,1;

运行时间：20ms

占用内存：3296k

-- 题目的关键信息是入职时间排名 排名的是入职时间
-- 先给入职时间排名(去重)，再从排名时间选出第三个，最后由时间对应员工输出员工信息
SELECT DISTINCT hire_date FROM employees ORDER BY hire_date DESC;
SELECT DISTINCT hire_date FROM employees ORDER BY hire_date DESC LIMIT 2,1;
SELECT * FROM employees WHERE hire_date = (SELECT DISTINCT hire_date FROM employees ORDER BY hire_date DESC LIMIT 2,1);

运行时间：22ms

占用内存：5480k

3. 查找各个部门当前(to_date='9999-01-01')领导当前薪水详情以及其对应部门编号dept_no

-- 按条件查询，三个条件：两表员工编号相同，部门管理表to_date=当前，薪水表to_date=当前
SELECT s.*, dm.dept_no 
FROM salaries s, dept_manager dm 
WHERE s.emp_no = dm.emp_no AND s.to_date = '9999-01-01' AND dm.to_date = '9999-01-01';

运行时间：24ms

占用内存：3296k

• 注意：这里有个坑!

主要在于两个表的逻辑关系，题目要求是薪水情况以及部门编号，再结合输出情况 dept_no 被放到了最后一列，可见是主表是“salaries”。
这里顺序错了就会提示：您的代码无法通过所有用例。

4. 查找所有已经分配部门的员工的last_name和first_name

-- 按条件查询 部门员工编号=员工编号
SELECT e.last_name, e.first_name, de.dept_no FROM employees e, dept_emp de WHERE e.emp_no = de.emp_no;

运行时间：16ms

占用内存：3424k

5. 查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括展示没有分配具体部门的员工

-- 员工表的emp_no关联dept_emp的emp_no 注意连接方式
SELECT e.last_name, e.first_name, de.dept_no 
FROM employees e LEFT JOIN dept_emp de 
ON e.emp_no = de.emp_no;

运行时间：16ms

占用内存：3428k

• 注意：三种JOIN连接的基本区别

INNER JOIN 两边表同时有对应的数据，即任何一边缺失数据就不显示;
LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据;
RIGHT JOIN 会读取右边数据表的全部数据，即便左边表无对应数据。

本题使用的LEFT JOIN：
LEFT JOIN（左连接）：获取左表employees所有记录，即使右表dept_emp没有对应匹配的dept_no记录。

• 注意：on与where的区别

两个表连接时用on，在使用left jion时，on和where条件的区别如下：
1、on 条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录；
2、where 条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

SQL中ON和WHERE的区别
【mySQL】left join、right join和join的区别

6. 查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按照emp_no进行逆序

-- 按条件查询 两个条件: 员工表职员编号=薪水表职员编号, 员工表hire_date=薪水表from_date
SELECT e.emp_no, s.salary 
FROM employees e, salaries s 
WHERE s.emp_no = e.emp_no AND s.from_date = e.hire_date 
ORDER BY e.emp_no DESC;

运行时间：20ms

占用内存：3420k

• 本题有以下四点需要注意：

1、由于测试数据中，salaries.emp_no 不唯一（因为号码为 emp_no 的员工会有多次涨薪的可能，所以在 salaries 中对应的记录不止一条），employees.emp_no 唯一，即 salaries 的数据会多于 employees，因此需先找到 employees.emp_no 在 salaries 表中对应的记录salaries.emp_no，则有限制条件 e.emp_no = s.emp_no
2、根据题意注意到 salaries.from_date 和 employees.hire_date 的值应该要相等，因此有限制条件 e.hire_date = s.from_date
3、根据题意要按照 emp_no 值逆序排列，因此最后要加上 ORDER BY e.emp_no DESC
4、为了代码良好的可读性，运用了 alias 别名语句，将 employees 简化为 e，salaries 简化为s，即 employees AS e 与 salaries AS s，其中 AS 可以省略

-- 另一种解法: 通过在薪水表找出同一职员的from_date, 最早的必定是入职时间, 而对应的薪水就是入职薪水
SELECT emp_no, salary FROM salaries GROUP BY emp_no HAVING MIN(from_date) ORDER BY emp_no DESC;

运行时间：21ms

占用内存：3420k

• 如果你使用命令后, 抛出以下异常信息:

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'employees.salaries.salary' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中，也就是说查出来的列必须在group by后面出现否则就会报错，或者这个字段出现在聚合函数里面。

mysql命令gruop by报错this is incompatible with sql_mode=only_full_group_by
https://www.cnblogs.com/jim2016/p/6322703.html

-- 第二种方法需要修改sql_mode(这样并不好) 可以通过添加临时表来解决问题
SELECT sf.emp_no, s.salary
FROM salaries AS s
INNER JOIN (
    SELECT emp_no, MIN(from_date) AS hire_date FROM salaries GROUP BY emp_no) AS sf
ON s.emp_no=sf.emp_no AND s.from_date=sf.hire_date
ORDER By sf.emp_no DESC;

运行时间：20ms

占用内存：3424k

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

-- 计数使用count 通过group by对每个员工编号分组 通过having进行条件筛选
SELECT emp_no, COUNT(emp_no) AS t FROM salaries GROUP BY emp_no HAVING t > 15;

运行时间：30ms

占用内存：3436k

• 本题有四个注意点：

1、用COUNT()函数和GROUP BY语句可以统计同一emp_no值的记录条数
2、根据题意，输出的涨幅次数为t，故用AS语句将COUNT(emp_no)的值转换为t
3、由于COUNT()函数不可用于WHERE语句中，故使用HAVING语句来限定t>15的条件
4、最后存在一个理解误区，涨幅超过15次，salaries中相应的记录数应该超过16（从第2条记录开始算作第1次涨幅），
不过题目为了简单起见，将第1条记录当作第1次涨幅，所以令t>15即可

• 注意：

严格来说，下一条salary高于本条才算涨幅，但本题只要出现了一条记录就算一次涨幅，salary相同可以理解为涨幅为0，salary变少理解为涨幅为负

8. 找出所有员工当前(to_date='9999-01-01')具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示

-- 当前to_date 相同薪水只显示一次-去重distinct 逆序order by desc
SELECT DISTINCT(salary) FROM salaries WHERE to_date="9999-01-01" ORDER BY salary DESC;

运行时间：21ms

占用内存：3432k

-- 大表一般用distinct效率不高，大数据量的时候都禁止用distinct，这时也可以group by解决重复问题
SELECT salary FROM salaries WHERE to_date="9999-01-01" GROUP BY salary ORDER BY salary DESC;

运行时间：20ms

占用内存：3424k

9. 获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary

当前表示to_date='9999-01-01'

-- dept_manager表获得manager的emp_no和dept_no, salaries表获得emp_no对应的salary
SELECT d.dept_no, d.emp_no, s.salary 
FROM dept_manager AS d , salaries AS s 
WHERE s.emp_no = d.emp_no AND s.to_date="9999-01-01" AND d.to_date="9999-01-01";

运行时间：22ms

占用内存：3304k

-- 使用双表连接也可以
SELECT d.dept_no, d.emp_no, s.salary 
FROM dept_manager AS d 
INNER JOIN salaries AS s 
ON s.emp_no = d.emp_no AND s.to_date="9999-01-01" AND d.to_date="9999-01-01";

运行时间：24ms

占用内存：4064k

• 本题有两个注意点：

1、先用INNER JOIN连接两张表，限制条件是两张表的emp_no相同，即d.emp_no = s.emp_no，并且将salaries用别名s代替，dept_manager用别名d代替
2、根据题意，要获取当前manager的当前salary情况，再加上限制条件d.to_date = '9999-01-01' AND s.to_date = '9999-01-01'即可（因为同一emp_no在salaries表中对应多条涨薪记录，而当s.to_date = '9999-01-01'时是该员工当前的薪水记录）

10. 获取所有非manager的员工emp_no

-- 从manager表中选出所有manager员工emp_no, 再从员工表反选
SELECT emp_no FROM employees WHERE emp_no NOT IN (SELECT emp_no FROM dept_manager);

运行时间：17ms

占用内存：3300k

-- 使用连接,选出交集为NULL的值即为不在manager表的emp_no
SELECT e.emp_no, d.dept_no FROM employees AS e LEFT JOIN dept_manager AS d ON e.emp_no=d.emp_no ORDER BY d.dept_no DESC;
SELECT t.emp_no 
FROM (
    SELECT e.emp_no, d.dept_no 
    FROM employees AS e 
    LEFT JOIN dept_manager AS d 
    ON e.emp_no=d.emp_no) AS t 
WHERE t.dept_no IS NULL;

运行时间：17ms

占用内存：3408k

-- 可以直接使用单层SELECT查询
SELECT e.emp_no FROM employees AS e LEFT JOIN dept_manager AS d ON e.emp_no=d.emp_no WHERE d.dept_no IS NULL;

运行时间：17ms

占用内存：3408k

• 注意：

NOT IN在实际使用中，因为NOT IN会转化成多表连接，而且不使用索引，在这里，觉得还是用LEFT JOIN代替会好一点

[[笔记] SQL性能优化 - 避免使用 IN 和 NOT IN] (https://www.cnblogs.com/hydor/p/5391556.html)

补充

本文格式

题目 + SQL代码 + 代码在审核系统中的耗时与资源
（+题目注意点+需要掌握的知识点）

SQL实战注意事项

• 有些题目是有坑的，字面上的意思和审核的标准可能是不同的
  比如：许多题目可能没写“当前”这个关键词，但审核结果是在“当前”的前提下的；
  可能因为计算关系，“薪水涨幅情况”其实是“薪水记录数”等等。
• 该实战的审核系统和运行环境（SQL 3.7.9）与当前我们的实际环境（比如MySQL 5.7+）是有比较大的区别的
  运行环境方面，所以有些语法实际环境上可能是无法得到正确答案，甚至有误的，
  审核系统方面，因为是在离线审核，所以数据量不太够或其他原因，无法正确识别正确代码
  以至其他有误的代码可以通过审核系统，所以我们最好将可以通过题目的答案仅做参考，主要从中吸取解题思路和技巧。

完整SQL代码

我的练习SQL代码已经上传至Github：https://github.com/slowbirdoflsh/newcode-sql-practice