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

最新推荐文章于 2023-07-04 09:04:02 发布

简说Python

最新推荐文章于 2023-07-04 09:04:02 发布

阅读量283

点赞数

本文授权转载自大数据肌肉猿

禁止二次转载

大家好，我是老表

阅读文本大概需要 15 分钟

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

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

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

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

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

第二十一题：查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序

SELECT  a.emp_no,  (a.salary - b.salary) AS growthFROM  (SELECT      e.emp_no,      s.salaryFROM      employees eLEFT JOIN salaries s ON e.emp_no = s.emp_noWHERE      s.to_date = '9999-01-01'GROUP BY      s.emp_no  ) a,  (SELECT      e.emp_no,      s.salaryFROM      employees eLEFT JOIN salaries s ON e.emp_no = s.emp_noWHERE      s.to_date IN (SELECTmin(to_date)FROM          salaries sGROUP BY          s.emp_no      )GROUP BY      s.emp_no  ) bWHERE  a.emp_no = b.emp_noORDER BY  growth ASC;

解题思路：

①先创建两个子查询，分别找出所有员工当前的薪水（如子查询a）和所有员工入职时的薪水（如子查询b）

②a，b子查询运用的方法都是一样的，联结employees表和salaries表，左联结内联结都可以，子查询a限定条件直接为当前时间'9999-01-01'，子查询b限定条件为入职时间，每个员工入职时间不一样，所以要使用谓词 in 找出在工资表里所有员工对应的最小时间。最后a，b子查询都要对员工编号进行分组。

③最后将这两个子查询联结，联结条件为a.emp_no = b.emp_no，再按照题目要求对薪水涨幅进行升序排序。

第二十二题：统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及次数sum

SELECT  a.dept_no,  a.dept_name,count(*) AS sumFROM  (SELECT      d.dept_no,      de.emp_no,      d.dept_nameFROM      dept_emp deLEFT JOIN departments d ON de.dept_no = d.dept_no  ) aINNER JOIN salaries s ON a.emp_no = s.emp_noGROUP BY  a.dept_no;

解题思路：将departments和dept_emp联结（左联结内联结都可以），得到员工对应的部门编号和部门名称后，再联结salaries表，通过对部门分组，得到每组工资数。

第二十三题：对所有员工的当前(to_date='9999-01-01')薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列

方法一：

SELECT  emp_no,  salary,dense_rank () over (ORDER BY salary DESC) AS 'rank'FROM  salariesWHERE to_date = '9999-01-01'ORDER BY  salary DESC,  emp_no ASC;

方法二：

SELECT  s1.emp_no,  s1.salary,count(DISTINCT s2.salary) AS 'rank'FROM  salaries AS s1INNER JOIN salaries AS s2 ON s1.to_date = '9999-01-01'AND s2.to_date = '9999-01-01'WHERE  s1.salary <= s2.salaryGROUP BY  s1.emp_noORDER BY  s1.salary DESC,  s1.emp_no ASC

解题思路：先说明两种方法都有一个小问题，就是使用别名rank时必须要用单引号或双引号，否则出错。

方法一：使用窗口函数DENSE_RANK() OVER()函数即可将实现薪水按照salary进行按照1-N的排名，相同salary并列。

窗口函数：<窗口函数> OVER ([PARITITION BY <列清单>] ORDER BY <排序用列清单>) [PARITITION BY <列清单>]可以省略。

①能够作为窗口函数的聚合函数（sum,avg,count,max,min）

②专用窗口函数（RANK,DENSE_RANK,ROW_NUMBER)

RANK() OVER (ORDER BY <列名>)：计算排序，如果存在相同位次的记录，则会跳过之后的位次。如1,1,1,4

DENSE_RANK () OVER (ORDER BY <列名>)：计算排序，即使存在相同位次的记录，也不会跳过之后的位次。如1,1,1,2

ROW_NUMBER () OVER (ORDER BY <列名>)：计算排序，赋予唯一的连续位次。如：1,2,3,4

方法二：

①利用复表查询，复用两张salaries进行比较排名，联结两表条件为to_date = '9999-01-01'。

②此外重点在于限定条件s1.salary <= s2.salary，在输出s1.salary的情况下，有多少个s2.salary大于等于s1.salary，即计数count(distinct s2.salary)，并以计数结果作为排序结果。

③由于两表联结，同样 salary 算作一样的排名，所以需要去重处理count(distinct s2.salary)。

④因为使用了合计函数，count只返回一个值，所以必须进行分组查询，最后使用order by排序。

第二十四题：获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date='9999-01-01'

方法一：

SELECT a.dept_no,
 a.emp_no,
 s.salaryFROM (
  SELECT
    de.emp_no,
    de.dept_no
  FROM
    dept_emp de
  INNER JOIN dept_manager dm ON de.dept_no = dm.dept_no
  WHERE
    de.emp_no <> dm.emp_no
  AND de.to_date = '9999-01-01'
) aINNER
JOIN salaries s ON a.emp_no = s.emp_noWHERE s.to_date = '9999-01-01';

方法二：

SELECT  de.dept_no,  e.emp_no,  s.salaryFROM  (    employees eINNER JOIN salaries s ON e.emp_no = s.emp_noAND s.to_date = '9999-01-01'  )INNER JOIN dept_emp de ON e.emp_no = de.emp_noWHERE  de.to_date = '9999-01-01'AND de.emp_no NOT IN (SELECT    emp_noFROM    dept_manager);

解题思路：

方法一：

①先将部门员工表dept_emp和部门经理表dept_manager联结，由dept_emp的员工编号和dept_manager的员工编号不相等找出非manager员工。

②再联结工资表找出工资，记得限定工资表salaries和员工表dept_emp的时间都为to_date='9999-01-01'

方法二：

①先联结员工表和工资表找出所有人的员工编号和工资，再联结部门员工表找出所有员工的编号，最后使用not in谓词，即使用子查询作为not in谓词的参数，找出非manager 的员工。

②最后记得限定工资表salaries和员工表dept_emp的时间都为to_date='9999-01-01'。

第二十五题：获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date='9999-01-01',

结果第一列给出员工的emp_no，

第二列给出其manager的manager_no，

第三列给出该员工当前的薪水emp_salary,

第四列给该员工对应的manager当前的薪水manager_salary

SELECT  a.emp_no,  b.emp_no AS manager_no,  a.emp_salary,  b.manager_salaryFROM  (    (SELECT        de.emp_no,        de.dept_no,        s.salary AS emp_salaryFROM        dept_emp de,        salaries sWHERE        de.emp_no = s.emp_noAND de.emp_no NOT IN (SELECT          dm.emp_noFROM          dept_manager dm      )AND s.to_date = '9999-01-01'    ) a,    (SELECT        de.emp_no,        de.dept_no,        s.salary AS manager_salaryFROM        dept_emp de,        salaries sWHERE        de.emp_no = s.emp_noAND de.emp_no IN (SELECT          dm.emp_noFROM          dept_manager dm      )AND s.to_date = '9999-01-01'    ) b  )WHERE  a.dept_no = b.dept_noAND a.emp_salary > b.manager_salary;

解题思路：

①先创建两个查询，分别找出非manager的员工编号、部门、薪水（如子查询a）和manager的员工编号，部门、薪水（如子查询b）

②a、b子查询都是联结dept_emp表和salaries工资表，使用 in 和 not in 谓词分别找出manager和员工，记得限定时间为当前。

③最后将两个子查询以部门编号联结，找出在同一个部门的manager和员工，限定条件为员工工资大于manager工资。

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

SELECT  de.dept_no,  dp.dept_name,  t.title,count(t.title) AS countFROM  (    titles tLEFT JOIN dept_emp de ON t.emp_no = de.emp_noAND t.to_date = '9999-01-01'AND de.to_date = '9999-01-01'  )INNER JOIN departments dp ON de.dept_no = dp.dept_noGROUP BY  de.dept_no,  t.title

解题思路：重点理解各个部门下各个title类型的汇总。

①先以员工编号为联结条件联结titles表和dept_emp表，得到员工部门编号和员工对应的title类型，再以部门编号为联结条件联结departments表，得到部门名称，最后记得限定titles表和dept_emp表的时间为当前to_date ='9999-01-01'。

②汇总的是各个部门下各个title类型，所以要先将部门编号进行一次汇总，再将tltle类型进行汇总，聚合函数count的参数为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_growthFROM  salaries s1INNER JOIN salaries s2 ON s1.emp_no = s2.emp_noWHERE  s2.salary - s1.salary > 5000AND (extract(YEAR FROM s2.to_date) - extract(YEAR FROM s1.to_date) = 1)ORDER BY  salary_growth DESC;

解题思路：

①利用复表查询，以emp_no为条件联结两表，用表2的salary减去表1的salary，得到salary_growth；

②使用extract函数截取年数，限定条件为表2年数减去表1年数等于一年，以及限定薪水涨幅大于5000，最后使用order by desc进行逆序排序。

③使用to_date的原因：from_date为进公司的时间，to_date的时间才是发工资的时间，可以理解为刚进公司那一天是没有工资的，to_date所代表的那一天才是一年工资到手的时间，所以应该用to_date相减。

④题目所给的答案有误，比如员工编号为10003，from_date为1995-12-03，salary_growth为24178的这条记录，图片是原表10003员工的所有数据，以1995-12-03为起点，一年后时间为1996-12-02，此时两者工资差为：43616-40006=3610，与答案所给的24178差之甚远。答案第二条记录也是这种错误。

第二十八题：查找描述信息中包括robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量>=5部

SELECT  c. NAME,count(f.film_id) AS countFROM  (SELECT      category_id,count(film_id) AS film_numFROM      film_categoryGROUP BY      category_idHAVINGcount(film_id) >= 5  ) a,  film f,category c,  film_category fcWHERE  f.description LIKE '%robot%'AND f.film_id = fc.film_idAND a.category_id = c.category_idAND c.category_id = fc.category_id;

解题思路：

①先用子查询在film_category表里电影各分类下电影数量大于等于5部的电影类别，再次基础上联结film，category和film_category表，这四个表都有对应的联结关系，不重不漏理清楚就好。

②使用谓词LIKE进行中间一致查询，作为限定条件。

第二十九题：使用join查询方式找出没有分类的电影id以及名称

SELECT  f.film_id,  f.titleFROM  film fLEFT JOIN film_category fc ON f.film_id = fc.film_idWHERE  fc.category_id IS NULL;

解题思路：使用film表为主表，左联结film_category表，限定条件为分类id为空的记录。

第三十题：使用子查询的方式找出属于Action分类的所有电影对应的title,description

SELECT  f.title,  f.descriptionFROM  film fWHERE  f.film_id IN (SELECT      fc.film_idFROM      film_category fcWHERE      fc.category_id IN (SELECT          c.category_idFROMcategory cWHERE          c. NAME = 'Action'      )  )

解题思路：使用两次子查询（子查询内嵌套子查询），通过IN谓词，film_id，category_id，将三个表层层嵌套查询。

大家好，我是老表
觉得本文不错的话，转发、留言、点赞，是对我最大的支持。

每日留言

说说你读完本文感受？

或者一句激励自己的话？

（字数不少于15字）

想进学习交流群

加微信：jjxksa888

备注：简说Python

2小时快速掌握Python基础知识要点。

完整Python基础知识要点

Python小知识 | 这些技能你不会?(一)

Python小知识 | 这些技能你不会?(二)

Python小知识 | 这些技能你不会?(三)

Python小知识 | 这些技能你不会?(四)

【2】【终篇】Pandas中文官方文档：基础用法6（含1-5)

觉得不错就点一下“在看”吧

简说Python

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
牛客网数据库SQL实战详细剖析(21-30)

本文授权转载自大数据肌肉猿禁止二次转载大家好，我是老表阅读文本大概需要 15分钟这是一个系列文章，总共61题，分6期，有答案以及解题思路，并附上解题的一个思考过程。具体题目可参考牛客网...
复制链接

扫一扫