牛客题霸 21-30

具体题目参考链接：https://www.nowcoder.com/ta/sql

---

20. 查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序(不包括已经离职的员工）

思路：
这题需要找到员工 入职时的薪水（即 hire_date = from_date 的 salary）和当前的薪水（即 to_date = ‘9999-01-01’）。
首先将 employees 表和 salaries 表连接，并限定 s1.to_date = ‘9999-01-01’，得到 所有员工最新的当前的那条 salary 记录（s1.salary），同时去除了所有已离职的员工记录。
然后将上一步的结果表继续与 salaries 表连接，并限定 e.hire_date = s2.from_date，得到 当前员工入职时的薪水（s2.salary）。
最后 s1.salary - s2.salary 即为薪水涨幅。

SELECT e.emp_no, (s1.salary - s2.salary) AS growth
FROM (employees AS e INNER JOIN salaries AS s1 ON e.emp_no = s1.emp_no AND s1.to_date = '9999-01-01')
INNER JOIN salaries AS s2 ON e.emp_no = s2.emp_no AND e.hire_date = s2.from_date
ORDER BY growth ASC;

---

22. 统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum，按照dept_no升序排序，

思路：多表连接时，一定要明白每一步连接得到的是什么！
首先，连接 dept_emp 表和 salaries 表，并按 de.dept_no 进行分组（由于分组的字段与 departments 表无关，所以暂时忽略第三行连接的 departments 表），得到每个部门的工资记录总数；
然后，将上一步的结果表与 departments 表连接，找到 dept_no 与 dept_name 的对应关系

SELECT de.dept_no, dp.dept_name, COUNT(s.salary) AS sum 
FROM (dept_emp AS de INNER JOIN salaries AS s ON de.emp_no = s.emp_no) 
INNER JOIN departments AS dp ON de.dept_no = dp.dept_no 
GROUP BY de.dept_no
ORDER BY de.dept_no ASC;

---

23. 对所有员工薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列

推荐阅读：通俗易懂的学会：SQL窗口函数

思路一：在支持窗口函数的数据库中（mysql不支持），可以使用窗口函数 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

SELECT emp_no, salary, DENSE_RANK() OVER(ORDER BY salary DESC) AS t_rank
FROM salaries
ORDER BY salary DESC, emp_no ASC

思路二：
1.注意！！使用别名rank时必须要用单引号或双引号，否则出错
2.采用多表查询，连接两表的条件为 s1.to_date = ‘9999-01-01’ AND s2.to_date = ‘9999-01-01’，相当于两张表做笛卡尔积，结果集的行数 为 两张表行数的乘积。
3.此题重点在于限定条件 s1.salary <= s2.salary，在输出 s1.salary 的情况下，有多少个 s2.salary 大于等于s1.salary。
4.由于两表联结，同样 salary 算作一样的排名，所以需要去重处理 count(distinct s2.salary)

（1）写法一
隐式内连接：可以省略inner join … on 关键字
select * from A,B where 条件

SELECT s1.emp_no, s1.salary, COUNT(DISTINCT s2.salary) AS rank
FROM salaries AS s1, salaries AS s2
WHERE s1.to_date = '9999-01-01'  AND s2.to_date = '9999-01-01' AND s1.salary <= s2.salary
GROUP BY s1.emp_no
ORDER BY s1.salary DESC, s1.emp_no ASC

（2）写法二
显式内连接：前提条件需要有外键，提交的关键字是 inner join … on
select * from A inner join B on 条件;

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

---

24. 获取所有非manager员工薪水情况，给出dept_no、emp_no以及salary

思路：三表连接。
首先连接 dept_emp 表和 dept_manager 表，并限定 de.emp_no != dm.emp_no，从而得到所有非 manager 员工的信息。
然后与 salaries 表连接，得到所有非manager员工薪水情况。

SELECT de.dept_no, de.emp_no, s.salary
FROM dept_emp AS de, dept_manager AS dm, salaries AS s
WHERE de.emp_no != dm.emp_no AND de.dept_no = dm.dept_no AND de.emp_no = s.emp_no;

---

25. 获取员工其当前的薪水比其manager当前薪水还高的相关信息，第一列给出员工的emp_no，第二列给出其manager的manager_no，第三列给出该员工当前的薪水emp_salary,第四列给该员工对应的manager当前的薪水manager_salary

思路：
首先连接 dept_emp 表和 salaries表，得到每个员工对应的薪水。
然后连接 dept_manager 表和 salaries 表，得到每个部门的经理对应的薪水。
最后以 de.dept_no = dm.dept_no 为连接条件，连接上面两张结果表，并限定 s1.salary > s2.salary 即可得到结果。

SELECT de.emp_no, dm.emp_no AS manager_no, s1.salary AS emp_salary, s2.salary AS manager_salary
FROM dept_emp AS de, salaries AS s1, dept_manager AS dm, salaries AS s2
WHERE s1.salary > s2.salary AND de.emp_no = s1.emp_no AND dm.emp_no = s2.emp_no AND de.dept_no = dm.dept_no;

---

26. 汇总各个部门当前员工的title类型的分配数目，即结果给出部门编号dept_no、dept_name、其部门下所有的员工的title以及该类型title对应的数目count，结果按照dept_no升序排序

思路：首先连接 dept_emp 表和 departments 表，连接条件为 de.dept_no = dp.dept_no，得到每个员工的部门对应的 名称。
然后，将上一步的表与 titles 表连接，得到每个员工的 title。
最后，关键在于按 de.dept_no 和 t.title 联合分组，统计相同部门、相同 title 的员工的个数。

SELECT de.dept_no, dp.dept_name, t.title, COUNT(de.emp_no) AS count
FROM dept_emp AS de, departments AS dp, titles AS t
WHERE de.dept_no = dp.dept_no AND de.emp_no = t.emp_no
GROUP BY de.dept_no, t.title
ORDER BY de.dept_no ASC;

---

28. 查找描述信息(film.description)中包含robot的电影对应的分类名称(category.name)以及电影数目(count(film.film_id))，而且还需要该分类包含电影总数量(count(film_category.category_id))>=5部

思路：这题有一个陷阱，电影数目>=5 是这类电影的所有数目，并不是包含了robot的这类电影的数目。所以，将 film 表、film_category 表、category 表连接好后，还需要和 筛选出电影数目>=5的 category 表连接。

SELECT c.name, COUNT(f.film_id) 
FROM (SELECT category_id, COUNT(film_id) FROM film_category GROUP BY category_id HAVING COUNT(film_id) >= 5) AS cc,
film AS f, film_category AS fc, category AS c
WHERE f.description LIKE '%robot%'
AND f.film_id = fc.film_id 
AND fc.category_id = c.category_id
AND c.category_id = cc.category_id;

---

29. 使用join查询方式找出没有分类的电影id以及名称

思路：运用 LEFT JOIN 连接两表，用 IS NULL 语句限定条件

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

---

30. 使用子查询的方式找出属于Action分类的所有电影对应的title,description

**思路：利用 IN 谓词，自内向外三层查询 **

SELECT title, description FROM film
WHERE film_id in (
    SELECT film_id FROM film_category 
    WHERE category_id in (
        SELECT category_id FROM category
        WHERE name = 'Action'
    )
);