牛客网数据库SQL实战详细剖析(11-20)

这是一个系列文章，总共61题，分6期，有答案以及解题思路，并附上解题的一个思考过程。

具体题目可参考牛客网的SQL实战模块：

https://www.nowcoder.com/ta/sql?page=0

一、牛客网数据库SQL实战详细剖析(1-10)

第十一题：获取所有员工当前的manager，如果当前的manager是自己的话结果不显示，当前表示to_date='9999-01-01'。

结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_no。

SELECT  de.emp_no,  dm.emp_no AS manager_no FROM  dept_emp deLEFT 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;

解题思路：重点要理解两表之间的逻辑关系和题目要求。有三个关键点：

①每个部门都有一个manager 每个人都有自己所在的部门。所以用部门号dept_no作为联结条件，左联结和内联结都可以；

②如果员工当前的manager是自己的话结果不显示，这就要求dept_emp表的员工编号emp_no和dept_manager的员工编号不相等；

③题目要求当前的manager，所以最后的经理表dept_manager的时间为to_date='9999-01-01'，严谨起见，员工表dept_emp的时间也应该是to_date='9999-01-01'。

 

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

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

解题思路：

①用GROUP BY d.dept_no将每个部门分为一组，用MAX()函数选取每组中工资最高者。

②为获得当前薪水，工资表和部门表的to_date都是'9999-01-01' 。

 

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

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

解题思路：使用group by将title分组，再用having子句找出大于等于2的组，having必须在group by之后，并且有group by才能使用having，聚合参数也可以是count(*) 和count(emp_no) ，还要注意>=号之间不能有空格，否则会出错。

 

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

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

解题思路：参考上题思路，同时使用distinct去重emp_no。

 

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

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

解题思路：员工号为奇数，则emp_no取余应为1，last_name不为Mary，用‘!=’表示，另外注意字符串要用单括号括起来，逆序用desc。

 

第十六题：统计出当前各个title类型对应的员工当前（to_date='9999-01-01'）薪水对应的平均工资。结果给出title以及平均工资avg。

SELECT  t.title,avg( salary ) AS avgFROM  salaries sINNER 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;

解题思路：使用group by 对title进行分组，当前用两表to_date='9999-01-01'进行限定，平均工资用聚合函数avg。

 

第十七题：获取当前（to_date='9999-01-01'）薪水第二多的员工的emp_no以及其对应的薪水salary

SELECT  emp_no,  salary FROM  salaries WHERE  salary = ( SELECT max( salary ) FROM salaries WHERE to_date = '9999-01-01' AND salary < ( SELECT max( salary ) FROM salaries ) );

解题思路：本题难点在于如何找出薪水排第二的员工，使用子查询里嵌套子查询。

①先在工资表里找出最多工资，再以工资小于最大工资为限定条件，即salary < (select max(salary)可以得到排名第二的工资。

②再使用max(salary)在除去排名第一工资的表里找最大的工资，即找到排名第二的工资。

③这个写法，不管工资第一多的员工有多少个，都能查到工资第二多的员工，且当工资第二多的员工有多个时，也都能查询出来，可以插入数据验证。

 

以下这种写法，尽管第一名工资有多个时仍然能查出工资第二多的，但当工资第二多的有多个员工的时候，只能查询出一个，不能查询出多个。

SELECT  emp_no,max( salary ) FROM  salaries WHERE  salary < ( SELECT max( salary ) FROM salaries ) AND to_date = '9999-01-01';

 

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

SELECT  e.emp_no,  s.salary,  e.last_name,  e.first_name FROM  employees eINNER JOIN salaries s ON e.emp_no = s.emp_no WHERE  salary = ( SELECT max( salary ) FROM salaries WHERE to_date = '9999-01-01' AND salary < ( SELECT max( salary ) FROM salaries ) );

解题思路：本题只需要在上一题的基础上增加一个内联结（左联结）即可将薪水salary、last_name以及first_name查询出来。

 

 

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

SELECT  last_name,  first_name,  dept_name FROM  (SELECT  e.last_name,  e.first_name,  de.dept_no FROM  employees eLEFT JOIN dept_emp de ON e.emp_no = de.emp_no   ) aLEFT JOIN departments dp ON a.dept_no = dp.dept_no;

解题思路：本题关键在于理清三表之间关联关系。需要使用两次左联结。红色部分可以省略。

①题目要求包括暂时没有分配部门的员工，所以将employees作为主表，使用左联结将其和dept_emp表联结，联结条件为员工编号emp_no，得出所有员工所在部门，并将结果命名为a。

②将查询结果a与departments左联结，联结条件为部门编号dept_no，求出部门名字dept_name。

 

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

方法一：

SELECTmax( salary ) - min( salary ) AS growth FROM  salaries WHERE  emp_no = '10001';

方法二：

SELECT( SELECT salary FROM salaries WHERE emp_no = '10001' ORDER BY to_date DESC LIMIT 1 ) - ( SELECT salary FROM salaries WHERE emp_no = '10001' ORDER BY to_date ASC LIMIT 1 ) AS growth;
FROM  salaries;

解题思路：对“入职以来的薪水salary涨幅值growth”有两种理解，第一种是员工入职以来工资最大值减去工资最小值，参考方法一。第二种理解是最新一次工资减去入职时的工资，即为涨幅值growth，参考方法二，方法二更严谨。

--end--

推荐阅读：

秋招失利，如何把握春招？(大四、研三)

计算机学生如何规划好大学四年的学习？

扫描下方二维码

添加好友，备注【交流群】

拉你到学习路线和资源丰富的交流群