2021-07-19 刷题 21、22、23题

SQL21 查找在职员工自入职以来的薪水涨幅情况

---

SQL 21 题目 SQL 21 题目 SQL 21 题目

解答：

本题应该考虑降薪的问题:

题中没有讲员工不降薪，所以不能直接使用最大薪水-最小薪水求值，例如：我入职薪水100元，过程中降薪20元，现在工资80元，那么按照（最大薪水-最小薪水求值）,薪水还涨了20元，但实际是降薪20元，所有有错误。

实际应该使用最后记录薪水-入职时薪水（80-100）,涨幅为-20元。

select b.emp_no,(b.salary-a.salary) as growth

from

(select e.emp_no,s.salary

from employees e left join salaries  s on e.emp_no=s.emp_no

and e.hire_date=s.from_date)a -- 入职工资表

inner join

(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')b -- 现在工资表

on a.emp_no=b.emp_no

order by growth

这道题写了很久，其实主要就是做两个表，然后刚入职的薪资减去现在的薪资，按照增长高低排序即可

---

SQL22 统计各个部门的工资记录数

---

SQL 22 题目 SQL 22 题目 SQL 22 题目 SQL 22 题目

解答（来源于讨论区）

方法1：嵌套查询，查出一个dept_no，就进行子查询的到对应COUNT()

SELECT d.dept_no,d.dept_name,count(s.salary) as sum

FROM salaries as s

LEFT join dept_emp as de on de.emp_no = s.emp_no

LEFT join departments as d on d.dept_no = de.dept_no

GROUP BY d.dept_no ORDER BY d.dept_no asc

注意：对于这种情形的子查询，一个部门进去，必须只返回一个值，而且只能查询一个列，可以参考26题的错误示范做对比

方法2：先进行两次内连接，再通过GROUP BY查询

SELECT de.dept_no, de.dept_name, COUNT(*) AS sum

FROM (SELECT *

FROM departments

INNER JOIN dept_emp

ON departments.dept_no=dept_emp.dept_no) AS de

INNER JOIN salaries AS s

ON de.emp_no=s.emp_no

GROUP BY de.dept_no;

注意：这里GROUP BY聚合dept_no,而dept_name看上去不是聚合列(因此似乎不能放进SELECT语句内)，但是事实上，由于dept_no是主键，所以一个dept_no只会对应一个dept_name，所以可以认为dept_name是可聚合的，所以可以放进去。特别要注意的是，如果dept_no不是主键，而是联合主键之一或者别的情况，总之dept_no不能唯一确定dept_name，那么即使在表中看上去dept_no和dept_name是一一对应的，也不能将dept_name放进SELECT中，强行放进去，在mysql中也会报错(当然在题库中不会报错)。

补充：内层的内连接，可以省略SELECT (因为内连接自己会生成临时表)：

SELECT de.dept_no, de.dept_name, COUNT(

) AS sum

FROM (departments

INNER JOIN dept_emp

ON departments.dept_no=dept_emp.dept_no) AS de

INNER JOIN salaries AS s

ON de.emp_no=s.emp_no

GROUP BY de.dept_no;

补充：还可以进一步省略，直接进行连续内连接：

SELECT d.dept_no, d.dept_name, COUNT(*) AS sum

FROM

(departments AS d

INNER JOIN dept_emp AS de

ON d.dept_no=de.dept_no

INNER JOIN salaries AS s

ON de.emp_no=s.emp_no)

GROUP BY de.dept_no;

注意：连续内连接中一定不要出现WHERE(21题的经验)

方法3：直接三表联查，用WHERE过滤

SELECT d.dept_no, d.dept_name, COUNT(*) AS sum

FROM departments AS d, dept_emp AS de, salaries AS s

WHERE d.dept_no=de.dept_no

AND de.emp_no=s.emp_no

GROUP BY d.dept_no;

备注：三表联查在代码上比进行两次内连接简洁

我的解答：将三个表连接再一起，然后利用count（）函数来计数，最后利用group by来分组，以及order by 来排序

SELECT d.dept_no,d.dept_name,count(s.salary) as sum

FROM salaries as s

LEFT join dept_emp as de

on de.emp_no = s.emp_no

LEFT join departments as d

on d.dept_no = de.dept_no

GROUP BY d.dept_no

ORDER BY d.dept_no asc

---

SQL23 对所有员工的薪水按照salary降序进行1-N的排名

---

SQL 23 题目 SQL 23 题目

解答（来源于讨论区）：

这道题目考察的是SQL窗口函数（OLAP函数）中用于排序的专用窗口函数用法

但是由于关系数据库提供支持OLAP用途功能时间不长

还有一部分DBMS不支持这个新功能（比如MYSQL）

select emp_no, salary,

       dense_rank() over (order by salary desc) as rank

from salaries

where to_date='9999-01-01'

order by rank asc,emp_no asc;

下面介绍三种用于进行排序的专用窗口函数：

1、RANK()

在计算排序时，若存在相同位次，会跳过之后的位次。

例如，有3条排在第1位时，排序为：1，1，1，4······

2、DENSE_RANK()

这就是题目中所用到的函数，在计算排序时，若存在相同位次，不会跳过之后的位次。

例如，有3条排在第1位时，排序为：1，1，1，2······

3、ROW_NUMBER()

这个函数赋予唯一的连续位次。

例如，有3条排在第1位时，排序为：1，2，3，4······

窗口函数用法：

<窗口函数> OVER ( [PARTITION BY <列清单> ]

                                ORDER BY <排序用列清单> ）

*其中[ ]中的内容可以忽略

我的解答：

SELECT emp_no,salary,dense_rank() over(ORDER BY salary DESC) as t_rank

FROM salaries

GROUP BY emp_no