牛客网数据库SQL实战详细剖析(21-30)

这是一个系列文章,总共61题,分6期,有答案以及解题思路,并附上解题的一个思考过程。


具体题目可参考牛客网的SQL实战模块:

https://www.nowcoder.com/ta/sql?page=0



一、牛客网数据库SQL实战详细剖析(1-10)

二、牛客网数据库SQL实战详细剖析(11-20)



第二十一题:查找所有员工自入职以来的薪水涨幅情况,给出员工编号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--

上一篇:[置顶].NET平台开源项目速览(4).NET文档生成工具ADB及使用


下一篇:Arrays工具类的使用(十七)上