这是一个系列文章，总共61题，分6期，有答案以及解题思路，并附上解题的一个思考过程。
具体题目可参考牛客网的SQL实战模块：
https://www.nowcoder.com/ta/sql?page=0
第二十一题：查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序
SELECT a.emp_no, (a.salary - b.salary) AS growthFROM (SELECT e.emp_no, s.salaryFROM employees eLEFT JOIN salaries s ON e.emp_no = s.emp_noWHERE s.to_date = '9999-01-01'GROUP BY s.emp_no ) a, (SELECT e.emp_no, s.salaryFROM employees eLEFT JOIN salaries s ON e.emp_no = s.emp_noWHERE s.to_date IN (SELECTmin(to_date)FROM salaries sGROUP BY s.emp_no )GROUP BY s.emp_no ) bWHERE a.emp_no = b.emp_noORDER 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 sumFROM (SELECT d.dept_no, de.emp_no, d.dept_nameFROM dept_emp deLEFT JOIN departments d ON de.dept_no = d.dept_no ) aINNER JOIN salaries s ON a.emp_no = s.emp_noGROUP 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 s1INNER JOIN salaries AS s2 ON s1.to_date = '9999-01-01'AND s2.to_date = '9999-01-01'WHERE s1.salary <= s2.salaryGROUP BY s1.emp_noORDER 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') aINNERJOIN salaries s ON a.emp_no = s.emp_noWHERE s.to_date = '9999-01-01';
方法二：
SELECT de.dept_no, e.emp_no, s.salaryFROM ( employees eINNER JOIN salaries s ON e.emp_no = s.emp_noAND s.to_date = '9999-01-01' )INNER JOIN dept_emp de ON e.emp_no = de.emp_noWHERE de.to_date = '9999-01-01'AND de.emp_no NOT IN (SELECT emp_noFROM 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_salaryFROM ( (SELECT de.emp_no, de.dept_no, s.salary AS emp_salaryFROM dept_emp de, salaries sWHERE de.emp_no = s.emp_noAND de.emp_no NOT IN (SELECT dm.emp_noFROM dept_manager dm )AND s.to_date = '9999-01-01' ) a, (SELECT de.emp_no, de.dept_no, s.salary AS manager_salaryFROM dept_emp de, salaries sWHERE de.emp_no = s.emp_noAND de.emp_no IN (SELECT dm.emp_noFROM dept_manager dm )AND s.to_date = '9999-01-01' ) b )WHERE a.dept_no = b.dept_noAND 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 countFROM ( titles tLEFT JOIN dept_emp de ON t.emp_no = de.emp_noAND t.to_date = '9999-01-01'AND de.to_date = '9999-01-01' )INNER JOIN departments dp ON de.dept_no = dp.dept_noGROUP 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_growthFROM salaries s1INNER JOIN salaries s2 ON s1.emp_no = s2.emp_noWHERE s2.salary - s1.salary > 5000AND (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 countFROM (SELECT category_id,count(film_id) AS film_numFROM film_categoryGROUP BY category_idHAVINGcount(film_id) >= 5 ) a, film f,category c, film_category fcWHERE f.description LIKE '%robot%'AND f.film_id = fc.film_idAND a.category_id = c.category_idAND c.category_id = fc.category_id;
解题思路：
①先用子查询在film_category表里电影各分类下电影数量大于等于5部的电影类别，再次基础上联结film，category和film_category表，这四个表都有对应的联结关系，不重不漏理清楚就好。
②使用谓词LIKE进行中间一致查询，作为限定条件。
第二十九题：使用join查询方式找出没有分类的电影id以及名称
SELECT f.film_id, f.titleFROM film fLEFT JOIN film_category fc ON f.film_id = fc.film_idWHERE fc.category_id IS NULL;
解题思路：使用film表为主表，左联结film_category表，限定条件为分类id为空的记录。
第三十题：使用子查询的方式找出属于Action分类的所有电影对应的title,description
SELECT f.title, f.descriptionFROM film fWHERE f.film_id IN (SELECT fc.film_idFROM film_category fcWHERE fc.category_id IN (SELECT c.category_idFROMcategory cWHERE c. NAME = 'Action' ) )
解题思路：使用两次子查询（子查询内嵌套子查询），通过IN谓词，film_id，category_id，将三个表层层嵌套查询。
--end--
