2021-07-19 刷题 21、22、23题
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
这道题写了很久，其实主要就是做两个表，然后刚入职的薪资减去现在的薪资，按照增长高低排序即可
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
