牛客网SQL刷题21-30

21、查找当前薪水(to_date=‘9999-01-01’)排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，你可以不使用order by完成吗

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

解答1：
select e.emp_no,max(s.salary),e.last_name,e.first_name from employees as e
inner join salaries as s
on e.emp_no = s.emp_no
where to_date = '9999-01-01'
and salary < (select max(salary) from salaries as s where s.to_date = '9999-01-01' )

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

CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`dept_no`));
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

解答：
SELECT d.dept_no, d.dept_name, count(s.salary) AS SUM
FROM departments d JOIN dept_emp de ON d.dept_no = de.dept_no
JOIN salaries s ON de.emp_no = s.emp_no
GROUP BY d.dept_no
ORDER BY d.dept_no ASC

23、对所有员工的当前(to_date=‘9999-01-01’)薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

-- rank排名：查询表中大于自己薪水的员工的数量（考虑并列：去重）
SELECT 
  s1.emp_no,
  s1.salary,
  (SELECT 
    COUNT(DISTINCT s2.salary) 
  FROM
    salaries s2 
  WHERE s2.to_date = '9999-01-01' 
    AND s2.salary >= s1.salary) AS `rank`  -- 去重：计算并列排名
FROM
  salaries s1 
WHERE s1.to_date = '9999-01-01' 
ORDER BY s1.salary DESC,
  s1.emp_no ;
  
  SELECT 
  emp_no,
  salary,
  dense_rank () over (
ORDER BY salary DESC) AS `rank` 
FROM
  salaries 
WHERE to_date = '9999-01-01' ;

下面介绍三种用于进行排序的专用窗口函数：
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 <排序用列清单> ）
*其中[ ]中的内容可以忽略

24、获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date=‘9999-01-01’

CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

答案：
 select de.dept_no,a.emp_no,s.salary from 
 (select emp_no from employees
 where emp_no not in
 (select emp_no from dept_manager ))a
inner join dept_emp de on a.emp_no=de.emp_no
inner join salaries s on  a.emp_no=s.emp_no
where s.to_date='9999-01-01';

25、获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date=‘9999-01-01’,

结果第一列给出员工的emp_no，
第二列给出其manager的manager_no，
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

这道题还是比较有意思的 简单的构建两个表 
分别为 员工工资表和经理工资表 
并且同时salaries.emp_no = '9999-01-01' 即可

select a.emp_no,b.emp_no,a.salary,b.salary  from 
(select de.emp_no,s.salary,de.to_date,de.dept_no
from dept_emp de join salaries s 
on de.emp_no=s.emp_no
and s.to_date='9999-01-01' )as a

join

(select dm.emp_no,s.salary,dm.dept_no
from dept_manager dm join salaries  s
on dm.emp_no=s.emp_no
and s.to_date='9999-01-01')as b

on a.salary>b.salary
and a.dept_no=b.dept_no;

26、汇总各个部门当前员工的title类型的分配数目，即结果给出部门编号dept_no、dept_name、其部门下所有的当前(dept_emp.to_date = ‘9999-01-01’)员工的当前(titles.to_date = ‘9999-01-01’)title以及该类型title对应的数目count，结果按dept_no升序排序

(注：因为员工可能有离职，所有dept_emp里面to_date不为’9999-01-01’就已经离职了，不计入统计，而且员工可能有晋升，所以如果titles.to_date 不为 ‘9999-01-01’，那么这个可能是员工之前的职位信息，也不计入统计)

CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`dept_no`));
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE IF NOT EXISTS `titles` (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

答案：
本题的关键在于用 GROUP BY 同时对 de.dept_no 和 t.title
进行分组，具体思路如下：
1、先用 INNER JOIN 连接 dept_emp 与 salaries，根据测试数据添加限定条件 de.to_date =
‘9999-01-01’ AND t.to_date = ‘9999-01-01’，即当前员工的当前头衔
2、再用 INNER JOIN 连接departments，限定条件为 de.dept_no =
dp.dept_no，即部门编号相同
3、最后用 GROUP BY 同时对 de.dept_no 和 t.title 进行分组，用 COUNT(t.title)
统计相同部门下相同头衔的员工个数

select d.dept_no,d.dept_name,t.title,count(t.title)as count
from departments as  d 
join dept_emp  de 
on d.dept_no=de.dept_no 
and de.to_date='9999-01-01'
join titles t 
on  t.to_date='9999-01-01'
and de.emp_no=t.emp_no
group by d.dept_no,t.title;

27、给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth，并按照salary_growth逆序排列。

提示：在sqlite中获取datetime时间对应的年份函数为strftime(’%Y’, to_date)
(数据保证每个员工的每条薪水记录to_date-from_date=1年，而且同一员工的下一条薪水记录from_data=上一条薪水记录的to_data)

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

提示：在sqlite中获取datetime时间对应的年份函数为strftime('%Y', to_date)
(数据保证每个员工的每条薪水记录to_date-from_date=1年，而且同一员工的下一条薪水记录from_data=上一条薪水记录的to_data)
select s1.emp_no,s2.from_date,(s2.salary-s1.salary)as salary_growth
from salaries s1
join salaries s2
on s1.emp_no=s2.emp_no
and (strftime('%Y',s2.to_date)-strftime("%Y",s1.to_date))=1
and (s2.salary-s1.salary)>5000
order by salary_growth desc;

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

select c.name,sum(description like '%robot%') cnt
from film a join film_category b on a.film_id=b.film_id
join category c on c.category_id=b.category_id
group by c.category_id
having count(*)>=5 and cnt>0;

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

CREATE TABLE IF NOT EXISTS film (
film_id smallint(5)  NOT NULL DEFAULT '0',
title varchar(255) NOT NULL,
description text,
PRIMARY KEY (film_id));

CREATE TABLE category  (
category_id  tinyint(3)  NOT NULL ,
name  varchar(25) NOT NULL, `last_update` timestamp,
PRIMARY KEY ( category_id ));

CREATE TABLE film_category  (
film_id  smallint(5)  NOT NULL,
category_id  tinyint(3)  NOT NULL, `last_update` timestamp);

答案：
select f.film_id,f.title
from film as f
left join film_category fc on f.film_id=fc.film_id
where fc.category_id is null

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

CREATE TABLE IF NOT EXISTS film (
film_id smallint(5)  NOT NULL DEFAULT '0',
title varchar(255) NOT NULL,
description text,
PRIMARY KEY (film_id));

CREATE TABLE category  (
category_id  tinyint(3)  NOT NULL ,
name  varchar(25) NOT NULL, `last_update` timestamp,
PRIMARY KEY ( category_id ));

CREATE TABLE film_category  (
film_id  smallint(5)  NOT NULL,
category_id  tinyint(3)  NOT NULL, `last_update` timestamp);
答案：
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'));