21查找所有员工自入职以来的薪水涨幅情况
题目描述
查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序
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 end.emp_no,(end.salary - start.salary) growth
from (select emp_no,salary from salaries where to_date = '9999-01-01') end
left join
(select s.emp_no,salary from salaries s inner join employees e on s.emp_no = e.emp_no where s.from_date = e.hire_date) start
on start.emp_no = end.emp_no order by growth
题解
1、获得当前工资表，放入临时表end中
-- 目前的工资
(select emp_no,salary from salaries where to_date = '9999-01-01') end
2、获得第一次入职时的工资，放入临时表start中
-- 最开始的工资
(select s.emp_no,salary from salaries s left join employees e on s.emp_no = e.emp_no where s.from_date = e.hire_date) start
3、连接临时表，用end工资-start工资即可
22统计各个部门对应员工涨幅的次数总和
题目描述
统计各个部门对应员工涨幅的次数总和，给出部门编码dept_no、部门名称dept_name以及次数sum
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 dept_no,dept_name,
sum(sum) sum
from
(select emp_no,d.dept_no,dept_name from dept_emp de left join departments d on de.dept_no = d.dept_no) info
inner join (select emp_no,count(salary) sum
from salaries s group by emp_no) s
on info.emp_no = s.emp_no group by dept_no
题解
1、计算每个员工的涨幅次数总和 s
-- 涨幅次数总和
(select emp_no,count(salary) sum from salaries s group by emp_no) s
2、拼接员工和部门信息 info
-- 部门信息
(select emp_no,d.dept_no,dept_name from dept_emp de left join departments d on de.dept_no = d.dept_no) info
3、拼接info和s表，对dept_no进行分组，计算每个部门对应的sum总和
23对所有员工的薪水按照salary进行按照1-N的排名
题目描述
对所有员工的当前(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`));
答案
select t1.emp_no,t1.salary,count(distinct t2.salary) rank
from (select * from salaries s where to_date = '9999-01-01') t1 ,
(select * from salaries s where to_date = '9999-01-01') t2
where t2.salary >= t1.salary
group by t1.emp_no
order by rank
题解
本题无法过MySQL的查询，但是测试通过了sqlite，也就是牛客网提交通过。
1、获得两个临时表t1,t2
(select * from salaries s where to_date = '9999-01-01') t1
(select * from salaries s where to_date = '9999-01-01') t2
2、用第一个临时表的t1.salary去和t2.salary进行比较，从何可以获得所有t1.salary小于t2.salary的数据集，正是如此，我们可以对用户进行分组，统计每个用户对应的salary小于其他用户的数据条数，此为排名。为什么count时要去重，因为可能存在相同排名的人
select t1.emp_no,count(distinct t2.salary) from t1,t2 where t2.salary >= t1.salary group by t1.emp_no
24获取所有非manager员工当前的薪水情况
题目描述
获取所有非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 dept_no,s.emp_no,salary from (select * from employees where emp_no not in (select emp_no from dept_manager)) emp, salaries s,dept_emp de where emp.emp_no = s.emp_no and s.to_date = '9999-01-01' and emp.emp_no = de.emp_no
题解
1、获取所有的manager的no
select emp_no from dept_manager
2、获取所有非manager的信息表 emp
select * from employees where emp_no not in (select emp_no from dept_manager)
3、拼接emp，salaries，dept_emp表 获取答案结果集
25获取员工其当前的薪水比其manager当前薪水还高的相关信息
题目描述
获取员工其当前的薪水比其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`));
答案
select t1.emp_no,t2.manager_no,t1.salary emp_salary,t2.manager_salary from (select de.emp_no,salary from dept_emp de,salaries s where de.emp_no not in (select dept_manager.emp_no from dept_manager) and de.emp_no = s.emp_no and s.to_date = '9999-01-01') t1,(select dm.emp_no manager_no,salary manager_salary,dept_no from dept_manager dm,salaries s where dm.emp_no = s.emp_no and s.to_date = '9999-01-01') t2,dept_emp de where t1.emp_no = de.emp_no and de.dept_no = t2.dept_no and t1.salary > manager_salary
题解
1、获取所有manager的工资信息 t2
select dm.emp_no manager_no,salary manager_salary,dept_no from dept_manager dm,salaries s where dm.emp_no = s.emp_no and s.to_date = '9999-01-01'
2、获取所有普通员工的工资信息 t1
select de.emp_no,salary from dept_emp de,salaries s where de.emp_no not in (select dept_manager.emp_no from dept_manager) and de.emp_no = s.emp_no and s.to_date = '9999-01-01'
3、拼接 t1,t2,dept_emp，通过条件过滤
26汇总各个部门当前员工的title类型的分配数目
题目描述
汇总各个部门当前员工的title类型的分配数目，结果给出部门编号dept_no、dept_name、其当前员工所有的title以及该类型title对应的数目count
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);
答案
select d.dept_no,d.dept_name,title,count(emp_no) count from (select emp_no,d.dept_no,title,dept_name from (select de.emp_no,dept_no,title from dept_emp de,titles t where de.emp_no = t.emp_no and t.to_date = '9999-01-01' and de.to_date = '9999-01-01') t1,departments d where t1.dept_no = d.dept_no) t1,departments d where t1.dept_no = d.dept_no group by t1.dept_no,title
题解
1、当前员工对用的title
select de.emp_no,dept_no,title from dept_emp de,titles t where de.emp_no = t.emp_no and t.to_date = '9999-01-01' and de.to_date = '9999-01-01'
2、当前员工对应的dept信息
select emp_no,d.dept_no,title,dept_name from (select de.emp_no,dept_no,title from dept_emp de,titles t where de.emp_no = t.emp_no and t.to_date = '9999-01-01' and de.to_date = '9999-01-01') t1,departments d where t1.dept_no = d.dept_no
3、罗列title对应的count，对dept_no和title进行分组
27给出每个员工每年薪水涨幅超过5000的员工编号emp_no
题目描述
给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth，并按照salary_growth逆序排列。
提示：在sqlite中获取datetime时间对应的年份函数为strftime('%Y', to_date)
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 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 salary_growth > 5000
AND (strftime("%Y",s2.to_date) - strftime("%Y",s1.to_date) = 1
OR strftime("%Y",s2.from_date) - strftime("%Y",s1.from_date) = 1 )
ORDER BY salary_growth DESC
题解
28查找描述信息中包括robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量>=5部
题目描述
film表
| 字段 |
说明 |
| film_id |
电影id |
| 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));
category表
| 字段 |
说明 |
| category_id |
电影分类id |
| name |
电影分类名称 |
| last_update |
电影分类最后更新时间 |
CREATE TABLE category (
category_id tinyint(3) NOT NULL ,
name varchar(25) NOT NULL, `last_update` timestamp,
PRIMARY KEY ( category_id ));
film_category表
| 字段 |
说明 |
| film_id |
电影id |
| category_id |
电影分类id |
| last_update |
电影id和分类id对应关系的最后更新时间 |
CREATE TABLE film_category (
film_id smallint(5) NOT NULL,
category_id tinyint(3) NOT NULL, `last_update` timestamp);
查找描述信息中包括robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量>=5部
答案
select name,count(fc.film_id) from (select fc.category_id,count(f.film_id) ac from film f left join film_category fc on f.film_id = fc.film_id group by category_id having count(f.film_id) >= 5) ac,film f,film_category fc,category c where f.film_id = fc.film_id and fc.category_id = c.category_id and c.category_id = ac.category_id and description like '%robot%';
题解
1、获取分类电影数量大于等于5的所有分类ac
select fc.category_id,count(f.film_id) ac from film f left join film_category fc on f.film_id = fc.film_id group by category_id having count(f.film_id) >= 5;
2、连接ac,film,film_category,category四表
3、过滤
29使用join查询方式找出没有分类的电影id以及名称
题目描述
film表
| 字段 |
说明 |
| film_id |
电影id |
| 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));
category表
| 字段 |
说明 |
| category_id |
电影分类id |
| name |
电影分类名称 |
| last_update |
电影分类最后更新时间 |
CREATE TABLE category (
category_id tinyint(3) NOT NULL ,
name varchar(25) NOT NULL, `last_update` timestamp,
PRIMARY KEY ( category_id ));
film_category表
| 字段 |
说明 |
| film_id |
电影id |
| category_id |
电影分类id |
| last_update |
电影id和分类id对应关系的最后更新时间 |
CREATE TABLE film_category (
film_id smallint(5) NOT NULL,
category_id tinyint(3) NOT NULL, `last_update` timestamp);
使用join查询方式找出没有分类的电影id以及名称
答案
select f.film_id,title from film f left join film_category fc on f.film_id = fc.film_id where fc.category_id is null
题解
1、左连接
2、筛选category_id为空的项
30使用子查询的方式找出属于Action分类的所有电影对应的title,description
题目描述
film表
| 字段 |
说明 |
| film_id |
电影id |
| 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));
category表
| 字段 |
说明 |
| category_id |
电影分类id |
| name |
电影分类名称 |
| last_update |
电影分类最后更新时间 |
CREATE TABLE category (
category_id tinyint(3) NOT NULL ,
name varchar(25) NOT NULL, `last_update` timestamp,
PRIMARY KEY ( category_id ));
film_category表
| 字段 |
说明 |
| film_id |
电影id |
| category_id |
电影分类id |
| last_update |
电影id和分类id对应关系的最后更新时间 |
CREATE TABLE film_category (
film_id smallint(5) NOT NULL,
category_id tinyint(3) NOT NULL, `last_update` timestamp);
使用子查询的方式找出属于Action分类的所有电影对应的title,description
答案
select title,description from film f where exists(select * from film_category fc,category c where fc.category_id = c.category_id and c.name = 'Action' and f.film_id = fc.film_id)
题解
exists子查询操作
