牛客网数据库SQL实战答案解析上篇

最新推荐文章于 2022-08-01 11:08:03 发布

quentinschuman

最新推荐文章于 2022-08-01 11:08:03 发布

阅读量1.1k

点赞数

分类专栏：数据库SQL

本文链接：https://blog.csdn.net/quentinschuman/article/details/83868604

版权

数据库SQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

部分解析摘自牛客网讨论分析

查找最晚入职的员工信息

SELECT * FROM employees WHERE hire_date = (SELECT MAX(hire_date) FROM employees)

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

SELECT * FROM employees ORDER BY hire_date DESC LIMIT 2,1

LIMIT m,n : 表示从第m+1条开始，取n条数据；
LIMIT n ：表示从第0条开始，取n条数据，是limit(0,n)的缩写。
本题limit 2,1 表示从第（2+1）条数据开始，取一条数据，即入职员工时间排名倒数第三的员工。

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

SELECT s.*, d.dept_no FROM salaries s ,  dept_manager d WHERE s.to_date='9999-01-01' AND d.to_date='9999-01-01' AND s.emp_no = d.emp_no

注意题目对输出字段顺序有要求，因此两个表的顺序不能互换

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

SELECT e.last_name,e.first_name,d.dept_no FROM dept_emp AS d NATURAL JOIN employees AS e

注意natural join的使用

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

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

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

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

SELECT e.emp_no,s.salary FROM employees e INNER JOIN salaries s ON e.emp_no = s.emp_no AND e.hire_date = s.from_date ORDER BY e.emp_no DESC;

此题应注意以下四个知识点：
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 可以省略
方法一：利用 INNER JOIN 连接两张表
SELECT e.emp_no, s.salary FROM employees AS e INNER JOIN salaries AS s
ON e.emp_no = s.emp_no AND e.hire_date = s.from_date
ORDER BY e.emp_no DESC
方法二：直接用逗号并列查询两张表
SELECT e.emp_no, s.salary FROM employees AS e, salaries AS s
WHERE e.emp_no = s.emp_no AND e.hire_date = s.from_date
ORDER BY e.emp_no DESC

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

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

此题应注意以下四点：
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变少理解为涨幅为负 **/

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

SELECT DISTINCT(salary) FROM salaries WHERE to_date = '9999-01-01' ORDER BY salary DESC;

获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary，当前表示to_date=‘9999-01-01’

SELECT d.dept_no,d.emp_no,s.salary FROM dept_manager d INNER JOIN salaries s ON d.emp_no = s.emp_no AND d.to_date = '9999-01-01' AND s.to_date = '9999-01-01';

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’时是该员工当前的薪水记录）

获取所有非manager的员工emp_no

SELECT emp_no FROM employees WHERE emp_no NOT IN (SELECT emp_no FROM dept_manager);

方法一：使用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 emp_no FROM (SELECT * FROM employees LEFT JOIN dept_manager
ON employees.emp_no = dept_manager.emp_no)
WHERE dept_no IS NULL
方法三：方法二的简版，使用单层SELECT语句即可
SELECT employees.emp_no FROM employees LEFT JOIN dept_manager
ON employees.emp_no = dept_manager.emp_no
WHERE dept_no IS NULL

获取所有员工当前的manager，如果当前的manager是自己的话结果不显示，当前表示to_date=‘9999-01-01’。结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_no。

SELECT de.emp_no,dm.emp_no manager_no FROM dept_emp de INNER JOIN dept_manager dm ON de.dept_no = dm.dept_no WHERE de.to_date = '9999-01-01' AND dm.to_date = '9999-01-01' AND de.emp_no != dm.emp_no;

本题应注意以下三点：
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 代替后输出

获取所有部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary

SELECT d.dept_no,d.emp_no,MAX(s.salary) salary FROM salaries s INNER JOIN dept_emp d ON d.emp_no = s.emp_no WHERE d.to_date = '9999-01-01' AND s.to_date = '9999-01-01' GROUP BY d.dept_no;

此题思路如下：
1、先用INNER JOIN连接两张表，限制条件是两张表的emp_no相同，即d.emp_no = s.emp_no；
2、选取每个员工当前的工资水平，用d.to_date = ‘9999-01-01’ AND s.to_date = '9999-01-01’作条件限制，因为此表中每条最新记录的 to_date 都用 9999-01-01 表示；
3、用GROUP BY d.dept_no将每个部门分为一组，用MAX()函数选取每组中工资最高者；
4、将salaries用s代替，dept_emp用d代替，最后将MAX(s.salary)用salary代替后输出。

从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。

SELECT title,COUNT(title) t FROM titles GROUP BY title HAVING t>= 2;

注意：每组个数大于等于2这个条件不要忽略了，另外，题目给出的建表语句有误，按照题目给出的建表语句建表时会报错，把题目建表语句中的"title"改为`title`即可。

从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。注意对于重复的emp_no进行忽略。

SELECT title,COUNT(DISTINCT(emp_no)) t FROM titles GROUP BY title HAVING t >= 2;

此题应注意以下三点：
1、先用GROUP BY title将表格以title分组，再用COUNT(DISTINCT emp_no)可以统计同一title值且不包含重复emp_no值的记录条数
2、根据题意，输出每个title的个数为t，故用AS语句将COUNT(DISTINCT emp_no)的值转换为t
3、由于WHERE后不可跟COUNT()函数，故用HAVING语句来限定t>=2的条件

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

SELECT * FROM employees WHERE emp_no%2 = 1 AND last_name != 'Mary' ORDER BY hire_date DESC;

统计出当前各个title类型对应的员工当前薪水对应的平均工资。结果给出title以及平均工资avg。

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

注意：两个date条件都要有，缺一不可，加上date的限定条件表示员工在职情况，默认为‘9999-01-01’表示员工默认当前在职。

获取当前（to_date=‘9999-01-01’）薪水第二多的员工的emp_no以及其对应的薪水salary

SELECT * FROM (SELECT emp_no,salary FROM salaries WHERE to_date = '9999-01-01' ORDER BY salaries.salary DESC LIMIT 2) es ORDER BY es.salary LIMIT 1;

注意：以上是我自己写的比较复杂的一个sql，虽然能得到正确结果，但是步骤比较复杂，看了别人的语句（推荐此写法）更加简单明了，如下：
select emp_no,salary from salaries where to_date = ‘9999-01-01’ order by salary desc limit 1,1

查找当前薪水(to_date=‘9999-01-01’)排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不准使用order by

SELECT s.emp_no,MAX(s.salary) salary,e.last_name,e.first_name FROM salaries s INNER JOIN employees e ON s.emp_no = e.emp_no WHERE s.to_date = '9999-01-01' AND s.salary NOT IN (SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01');

本题做法很多，主要思想为多层SELECT嵌套与MAX()函数结合
1、先利用MAX()函数找出salaries中当前薪水最高者，即SELECT MAX(salary) FROM salaries WHERE to_date = ‘9999-01-01’
2、再利用INNER JOIN连接employees与salaries表，限定条件为【同一员工】e.emp_no = s.emp_no、【当前】s.to_date = '9999-01-01’与【非薪水最高】s.salary NOT IN (SELECT MAX(salary) FROM salaries WHERE to_date = ‘9999-01-01’)
3、在以上限制条件下找薪水最高者，即为所有员工薪水的次高者

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

SELECT de.last_name,de.first_name,d.dept_name FROM (SELECT e.last_name,e.first_name,de.dept_no FROM employees e LEFT JOIN dept_emp de ON de.emp_no = e.emp_no) de LEFT JOIN departments d ON d.dept_no = de.dept_no;

注意：本题我最开始使用SELECT de.last_name,de.first_name,d.dept_name FROM departments d RIGHT JOIN (SELECT e.last_name,e.first_name,de.dept_no FROM dept_emp de RIGHT JOIN employees e ON de.emp_no = e.emp_no) de ON d.dept_no = de.dept_no来做发现报错sqlite3.OperationalError: RIGHT and FULL OUTER JOINs are not currently supported，这是因为sqlite数据库不支持（+），不支持右外连接（right outer join）和全外连接(full outer join)，但支持左外连接（left outer join）。我们可以利用这一点来得到全外连接的结果。将右外连接改为左外连接即可。代码如上

查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth

SELECT (s.max - s.min) growth FROM (SELECT MAX(salary) max,MIN(salary) min FROM salaries WHERE emp_no = '10001') s;

本题思路很简单，找到该员工薪水的最大值和最小值，其差值就是涨幅growth

查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅

select a.emp_no, (b.salary - c.salary) as growth from employees as a inner join salaries as b on a.emp_no = b.emp_no and b.to_date = '9999-01-01' inner join salaries as c on a.emp_no = c.emp_no and a.hire_date = c.from_date order by growth asc;

这题貌似有点复杂，绕半天没绕出来，答案来自牛客网讨论组，后面再慢慢细看

统计各个部门对应员工涨幅的次数总和，给出部门编码dept_no、部门名称dept_name以及次数sum

SELECT dm.dept_no,dm.dept_name,COUNT(s.salary) sum FROM (dept_emp de INNER JOIN salaries s ON de.emp_no = s.emp_no) INNER JOIN departments dm ON de.dept_no = dm.dept_no GROUP BY de.dept_no;

本题关键是要将每个部门分组，并分别统计工资记录总数，思路如下：
1、用INNER JOIN连接dept_emp表和salaries表，并以dept_emp.no分组，统计每个部门所有员工工资的记录总数
2、再将上表用INNER JOIN连接departments表，限制条件为两表的dept_no相等，找到dept_no与dept_name的对应关系，最后依次输出dept_no、dept_name、sum

对所有员工的当前(to_date=‘9999-01-01’)薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列

SELECT s1.emp_no, s1.salary, COUNT(DISTINCT s2.salary) AS rank
FROM salaries AS s1, salaries AS s2
WHERE s1.to_date = '9999-01-01'  AND s2.to_date = '9999-01-01' AND s1.salary <= s2.salary
GROUP BY s1.emp_no
ORDER BY s1.salary DESC, s1.emp_no ASC

本题题目看起来简单明了，实际实现并不简单，涉及到的点也比较多，答案来自牛客网，先Mark一下，后面慢慢看。
本题的主要思想是复用salaries表进行比较排名，具体思路如下：
1、从两张相同的salaries表（分别为s1与s2）进行对比分析，先将两表限定条件设为to_date = ‘9999-01-01’，挑选出当前所有员工的薪水情况。
2、本题的精髓在于 s1.salary <= s2.salary，意思是在输出s1.salary的情况下，有多少个s2.salary大于等于s1.salary，比如当s1.salary=94409时，有3个s2.salary（分别为94692,94409,94409）大于等于它，但由于94409重复，利用COUNT(DISTINCT s2.salary)去重可得工资为94409的rank等于2。其余排名以此类推。
3、千万不要忘了GROUP BY s1.emp_no，否则输出的记录只有一条（可能是第一条或者最后一条，根据不同的数据库而定），因为用了合计函数COUNT()
4、最后先以 s1.salary 逆序排列，再以 s1.emp_no 顺序排列输出结果

获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date=‘9999-01-01’

SELECT de.dept_no, s.emp_no, s.salary 
FROM (employees AS e INNER JOIN salaries AS s ON s.emp_no = e.emp_no AND s.to_date = '9999-01-01')
INNER JOIN dept_emp AS 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')

1、先用INNER JOIN连接employees和salaries，找出当前所有员工的工资情况
2、再用INNER JOIN连接dept_emp表，找到所有员工所在的部门
3、最后用限制条件de.emp_no NOT IN (SELECT emp_no FROM dept_manager WHERE to_date = ‘9999-01-01’)选出当前所有非manager员工，再依次输出dept_no、emp_no、salary
此外，还能作如下简化，不连接employees表也能完成：
SELECT de.dept_no, s.emp_no, s.salary
FROM dept_emp AS de INNER JOIN salaries AS s ON s.emp_no = de.emp_no AND s.to_date = ‘9999-01-01’
WHERE de.emp_no NOT IN (SELECT emp_no FROM dept_manager WHERE to_date = ‘9999-01-01’)

获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date=‘9999-01-01’,
结果第一列给出员工的emp_no，
第二列给出其manager的manager_no，
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary

SELECT sem.emp_no AS emp_no, sdm.emp_no AS manager_no, sem.salary AS emp_salary, sdm.salary AS manager_salary
FROM (SELECT s.salary, s.emp_no, de.dept_no FROM salaries s INNER JOIN dept_emp de
ON s.emp_no = de.emp_no AND s.to_date = '9999-01-01' ) AS sem, 
(SELECT s.salary, s.emp_no, dm.dept_no FROM salaries s INNER JOIN dept_manager dm
ON s.emp_no = dm.emp_no AND s.to_date = '9999-01-01' ) AS sdm
WHERE sem.dept_no = sdm.dept_no AND sem.salary > sdm.salary

答案来自牛客网，Mark
本题主要思想是创建两张表（一张记录当前所有员工的工资，另一张只记录部门经理的工资）进行比较，具体思路如下：
1、先用INNER JOIN连接salaries和demp_emp，建立当前所有员工的工资记录sem
2、再用INNER JOIN连接salaries和demp_manager，建立当前所有员工的工资记录sdm
3、最后用限制条件sem.dept_no = sdm.dept_no AND sem.salary > sdm.salary找出同一部门中工资比经理高的员工，并根据题意依次输出emp_no、manager_no、emp_salary、manager_salary

汇总各个部门当前员工的title类型的分配数目，结果给出部门编号dept_no、dept_name、其当前员工所有的title以及该类型title对应的数目count

SELECT dm.dept_no,dm.dept_name,t.title,COUNT(t.title) count FROM
(titles t INNER JOIN dept_emp de ON t.emp_no = de.emp_no AND t.to_date = '9999-01-01' AND de.to_date = '9999-01-01')
INNER JOIN departments dm ON de.dept_no = dm.dept_no GROUP BY dm.dept_no,t.title;

本题的关键在于用 GROUP BY 同时对 de.dept_no 和 t.title 进行分组，具体思路如下：
1、先用 INNER JOIN 连接 dept_emp 与 salaries，根据测试数据添加限定条件 de.to_date = ‘9999-01-01’ AND t.to_date = ‘9999-01-01’，即当前员工的当前头衔
2、再用 INNER JOIN 连接departments，限定条件为 de.dept_no = dp.dept_no，即部门编号相同
3、最后用 GROUP BY 同时对 de.dept_no 和 t.title 进行分组，用 COUNT(t.title) 统计相同部门下相同头衔的员工个数

给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth，并按照salary_growth逆序排列。
提示：在sqlite中获取datetime时间对应的年份函数为strftime(’%Y’, to_date)

SELECT s2.emp_no, s2.from_date, (s2.salary - s1.salary) AS salary_growth
FROM salaries AS s1, salaries AS s2
WHERE s1.emp_no = s2.emp_no 
AND salary_growth > 5000
AND (strftime("%Y",s2.to_date) - strftime("%Y",s1.to_date) = 1 
     OR strftime("%Y",s2.from_date) - strftime("%Y",s1.from_date) = 1 )
ORDER BY salary_growth DESC

答案来自牛客网，Mark
本题的难点在于如何理解每年薪水涨幅，以及复用salaries表求出每年薪水涨幅超过5000的员工，具体思路如下：
1、假设s1是涨薪水前的表，s2是涨薪水后的表，因为每个员工涨薪水的时间不全固定，有可能一年涨两次，有可能两年涨一次，所以每年薪水的涨幅，应该理解为两条薪水记录的from_date相同或to_date相同。
如果只限定to_date相同，则将第三条原始测试数据的52668改成62668时，就会少一条【62668-48584=14084】的记录
INSERT INTO salaries VALUES(10008,46671,‘1998-03-11’,‘1999-03-11’);
INSERT INTO salaries VALUES(10008,48584,‘1999-03-11’,‘2000-03-10’);
INSERT INTO salaries VALUES(10008, 62668 ,‘2000-03-10’,‘2000-07-31’);
2、找到s1与s2符合要求的记录后，用s2的薪水减去s1的薪水，用salary_growth表示，加上限定条件 s1.emp_no = s2.emp_no AND salary_growth > 5000，即同一员工每年涨幅超过5000的记录
3、最后依次输出emp_no、from_date、salary_growth，并以salary_growth逆序排列