做题笔记:SQL Sever 方式做牛客SQL的题目--SQL215

2023-12-14 16:42:46

----SQL215 查找在职员工自入职以来的薪水涨幅情况
查询在职员工自入职以来的薪水涨幅情况,给出在职员工编号emp_no以及其对应的薪水涨幅growth,并按照growth进行升序,
输出为:emp_no | growth
(注: to_date为薪资调整某个结束日期,或者为离职日期,to_date='9999-01-01’时,表示依然在职,无后续调整记录)

表的创建及数据插入:

drop table if exists  employees ;
drop table if exists  salaries ;
CREATE TABLE employees (
emp_no int 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 NOT NULL,
salary int NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','2001-06-22');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1999-08-03');
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1999-08-03','2000-08-02');
INSERT INTO salaries VALUES(10002,72527,'2000-08-02','2001-08-02');

查询如下:

select t1.emp_no, t2.salary - t1.salary as growth
from (
    select e.emp_no,salary
    from employees e
    join salaries s on e.emp_no = s.emp_no
    where hire_date = from_date
       ) t1
join (
    select e.emp_no,salary
    from employees e
    join salaries s on e.emp_no = s.emp_no
    where to_date = '9999-01-01'
    ) t2
on t1.emp_no = t2.emp_no
order by growth

文章来源:https://blog.csdn.net/emmmheng/article/details/134924529
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。