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

有关SQL 2019-12-27

205

这是一个系列文章，总共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 growth
FROM
  (
SELECT
      e.emp_no,
      s.salary
FROM
      employees e
LEFT JOIN salaries s ON e.emp_no = s.emp_no
WHERE
      s.to_date = '9999-01-01'
GROUP BY
      s.emp_no
  ) a,
  (
SELECT
      e.emp_no,
      s.salary
FROM
      employees e
LEFT JOIN salaries s ON e.emp_no = s.emp_no
WHERE
      s.to_date IN (
SELECT
min(to_date)
FROM
          salaries s
GROUP BY
          s.emp_no
      )
GROUP BY
      s.emp_no
  ) b
WHERE
  a.emp_no = b.emp_no
ORDER 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 sum
FROM
  (
SELECT
      d.dept_no,
      de.emp_no,
      d.dept_name
FROM
      dept_emp de
LEFT JOIN departments d ON de.dept_no = d.dept_no
  ) a
INNER JOIN salaries s ON a.emp_no = s.emp_no
GROUP 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 s1
INNER JOIN salaries AS s2 ON s1.to_date = '9999-01-01'
AND s2.to_date = '9999-01-01'
WHERE
s1.salary <= s2.salary
GROUP BY
s1.emp_no
ORDER 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.salary
FROM
  (
    employees e
INNER JOIN salaries s ON e.emp_no = s.emp_no
AND s.to_date = '9999-01-01'
  )
INNER JOIN dept_emp de ON e.emp_no = de.emp_no
WHERE
  de.to_date = '9999-01-01'
AND de.emp_no NOT IN (
SELECT
    emp_no
FROM
    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_salary
FROM
(
(
SELECT
de.emp_no,
de.dept_no,
s.salary AS emp_salary
FROM
dept_emp de,
salaries s
WHERE
de.emp_no = s.emp_no
AND de.emp_no NOT IN (
SELECT
dm.emp_no
FROM
dept_manager dm
)
AND s.to_date = '9999-01-01'
) a,
(
SELECT
de.emp_no,
de.dept_no,
s.salary AS manager_salary
FROM
dept_emp de,
salaries s
WHERE
de.emp_no = s.emp_no
AND de.emp_no IN (
SELECT
dm.emp_no
FROM
dept_manager dm
)
AND s.to_date = '9999-01-01'
) b
)
WHERE
a.dept_no = b.dept_no
AND 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 count
FROM
  (
    titles t
LEFT 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 dp ON de.dept_no = dp.dept_no
GROUP 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_growth
FROM
  salaries s1
INNER JOIN salaries s2 ON s1.emp_no = s2.emp_no
WHERE
  s2.salary - s1.salary > 5000
AND (
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 count
FROM
  (
SELECT
      category_id,
count(film_id) AS film_num
FROM
      film_category
GROUP BY
      category_id
HAVING
count(film_id) >= 5
  ) a,
  film f,
category c,
  film_category fc
WHERE
  f.description LIKE '%robot%'
AND f.film_id = fc.film_id
AND a.category_id = c.category_id
AND c.category_id = fc.category_id;

解题思路：

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

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

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

SELECT
f.film_id,
f.title
FROM
film f
LEFT JOIN film_category fc ON f.film_id = fc.film_id
WHERE
fc.category_id IS NULL;

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

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

SELECT
f.title,
f.description
FROM
film f
WHERE
f.film_id IN (
SELECT
fc.film_id
FROM
film_category fc
WHERE
fc.category_id IN (
SELECT
c.category_id
FROM
category c
WHERE
c. NAME = 'Action'
)
)

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

--end--

推荐阅读：

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

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

扫描下方二维码

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

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

最后修改时间：2020-06-09 16:07:19

文章转载自有关SQL，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

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

评论