牛客SQL二刷（25-30）

25、获取员工其当前的薪水比其manager当前薪水还高的相关信息

这道题有三张表，员工表、经理表、工资表，首先是需要把几张表联结，然后获取员工和经理对应，再根据薪水条件过滤。
说的轻巧，那么应该怎么去写这个语句呢。

本题主要思想是创建两张表（一张记录当前所有员工的工资，另一张只记录部门经理的工资）进行比较，具体思路如下：
1、先用INNER JOIN连接salaries和demp_emp，建立当前所有员工的工资记录sem
2、再用INNER JOIN连接salaries和demp_manager，建立当前所有部门经理的工资记录sdm
3、最后用限制条件sem.dept_no = sdm.dept_no AND sem.salary > sdm.salary找出同一部门中工资比经理高的员工，并根据题意依次输出emp_no、manager_no、emp_salary、manager_salary

思路真是妙啊！反正是非常的清晰，来自牛客讨论区的大佬ciphersaw，反正我觉得只要在牛客刷过SQL练习的都知道这个人！

虽然我现在是SQL菜鸟，但是跟着大佬好走路不是！

下面给出语句：

SELECT sem.emp_no AS emp_no, 
       sdm.emp_no AS manager_no, 
       sem.salary AS emp_salary, 
       sdm.salary AS manager_salary
FROM (SELECT s.salary, s.emp_no, de.dept_no 
      FROM salaries AS s 
      INNER JOIN dept_emp AS de
      ON s.emp_no = de.emp_no 
      AND s.to_date = '9999-01-01' ) AS sem, -- 第一张员工工资表
     (SELECT s.salary, s.emp_no, dm.dept_no 
      FROM salaries AS s 
      INNER JOIN dept_manager AS dm
      ON s.emp_no = dm.emp_no 
      AND s.to_date = '9999-01-01' ) AS sdm -- 第二张经理工资表
WHERE sem.dept_no = sdm.dept_no 
AND sem.salary > sdm.salary

26、汇总各个部门当前员工的title类型的分配数目

这道题首先看到输出结构我们就明白是什么意思了，也就是说将员工表先按照部门分组再按照title分组即可，然后COUNT计数。

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

27、给出每个员工每年薪水涨幅超过5000的员工编号emp_no

这道题的话重点在于求涨幅，之前我们好像做过类似的题目，要点就在于一表复用。题眼是每年，这里还要把日期字段处理一下。

SELECT s2.emp_no, 
       s2.from_date, 
       (s2.salary - s1.salary) AS salary_growth
FROM salaries AS s1, 
     salaries AS s2
WHERE s1.emp_no = s2.emp_no
AND s2.salary - s1.salary > 5000
AND strftime('%Y',s2.to_date) - strftime('%Y',s1.to_date) = 1
ORDER BY salary_growth DESC

-- 条件：每年薪水涨幅都超过5000
-- 技巧：复用salaries表

28、查找描述信息中包括robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量>=5

首先这道题，包括robot要用到 LIKE ‘%robot%’。其次我们要求电影名称对应的分类以及分类对应的名称，这些信息分散在不同的表中。原始表有三张，这里我们还构造了一个临时表满足过滤条件。

SELECT c.name, COUNT(f.film_id)
FROM film AS f, 
     film_category AS fc, 
     category AS c,
     (SELECT category_id 
      FROM film_category 
      GROUP BY category_id HAVING COUNT(category_id) >= 5) AS ci 
      -- 构造一个表即满足各分类电影数量>=5的条件
WHERE f.description LIKE '%robot%'
AND f.film_id = fc.film_id
AND fc.category_id = c.category_id
AND c.category_id = ci.category_id

这个题解我们发现没有用ON联结，而是在WHERE里联结的。

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

这道题其实明摆着可以用子查询NOT IN的，但是这里说用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

子查询么那么就是层层嵌套咯。

-- 1.找出属于Action分类的对应的分类名称
-- 2.找出该名称对应的电影id
-- 3.找出该电影id对应的电影名称和描述

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