牛客网SQL实战练习——16~20

声明：练习牛客网SQL实战题目，整理笔记。
16.统计出当前各个title类型对应的员工当前（to_date='9999-01-01'）薪水对应的平均工资。结果给出title以及平均工资avg。

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`));
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);

分析：本题中的两个表需要用inner by连接起来，平均薪水使用avg(s.salary) ，各个title类型group by title
答案：

select t.title,avg(s.salary) 
from titles as t inner join salaries as s
on t.emp_no=s.emp_no
where t.to_date='9999-01-01' and s.to_date='9999-01-01'
group by title

17.获取当前（to_date='9999-01-01'）薪水第二多的员工的emp_no以及其对应的薪水salary

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`));

分析：获取薪水第二多的员工信息，需要将salary进行排序，然后使用limit提出第二个薪水多的员工信息即可，即order by salary desc limit 1,1
答案：

select emp_no,salary
from salaries
where to_date='9999-01-01'
order by salary desc limit 1,1

18.查找当前薪水(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`));

分析：本题中明确指出不能使用order by，所以不能将salary进行排序，换种思路，查找排名第二多的员工信息，可以排除salary最高的员工，剩下的salary最高的便是第二高的，故使用max与not in和嵌套子查询完成。
答案：

select e.emp_no,max(s.salary) as salary,e.last_name,e.first_name
from employees as e inner join salaries as s
on e.emp_no=s.emp_no
where s.to_date='9999-01-01' 
and s.salary not in(
    select max(salary) 
    from salaries
    where to_date='9999-01-01'
)

19.查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工

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 `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`));

分析：第一步，将员工表employees与员工-部门关系表dept_emp进行外连接left join，连接条件是员工编号对应，这样得到的连表包含所有的员工信息+对应的部门编号信息（如果员工未分配部门，则按照外连接规则，默认填写null）；
第二步，将第一步中得到的连表，与部门信息表departments进行外连接，连接条件是部门编号对应，这样得到的新的连表即包含题目要求的所有记录。
答案：

select e.last_name,e.first_name,d.dept_name
from employees e 
left join dept_emp de on e.emp_no = de.emp_no
left join departments d on de.dept_no = d.dept_no

20.查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth

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`));

分析：首先分别找到emp_no=10001的员工的第一次工资记录与最后一次工资记录，再将最后一次工资记录减去第一次工资记录得到入职以来salary的涨幅，最后用别名growth代替。
答案：

select 
(
(select salary from salaries 
 where emp_no = 10001
 order by to_date desc
 limit 1
 )
 -
(select salary from salaries
 where emp_no = 10001
 order by to_date asc
 limit 1
 )
)
 as growth

欢迎关注微信公众号：蛋炒番茄
同步更新！！！