描述
有一个员工表employees简况如下:
emp_no | birth_date | first_name | last_name | gender | hire_date |
10001 | 1953-09-02 | Georgi | Facello | M | 2001-06-22 |
10002 | 1964-06-02 | Bezalel | Simmel | F | 1999-08-03 |
有一个薪水表salaries简况如下:
emp_no | salary | from_date | to_date |
10001 | 85097 | 2001-06-22 | 2002-06-22 |
10001 | 88958 | 2002-06-22 | 9999-01-01 |
10002 | 72527 | 1999-08-03 | 2000-08-02 |
10002 | 72527 | 2000-08-02 | 2001-08-02 |
请你查找在职员工自入职以来的薪水涨幅情况,给出在职员工编号emp_no以及其对应的薪水涨幅growth,并按照growth进行升序,以上例子输出为
(注: to_date为薪资调整某个结束日期,或者为离职日期,to_date='9999-01-01'时,表示依然在职,无后续调整记录)
emp_no | growth |
10001 | 3861 |
示例1
输入:
drop table if exists `employees` ; drop table if exists `salaries` ; 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`)); 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');
复制输出:
10001|3861
对于每个员工,自入职以来的薪水涨幅是:当前的薪水-入职时的薪水。
表是这样的:
需要分别求得入职时的工资与当前时间“ 9999-01-01”对应的工资。
先限定员工号为“ 1”。
拆开来看,先取得当前时间的工资:把员工表e与薪水表通过e.to_date =“ 9999-01-01”用inner join进行连接:(只显示重要列)
SQL 语句:
select e.emp_no,from_date,to_date,salary,hire_date
from employees e inner join salaries s on s.to_date='9999-01-01' and e.emp_no = 1;
接下来,获取入职时的工资,把员工表e与薪水表s通过e.hire_date = s.from_date用内部联接进行连接:
SQL 语句:
select e.emp_no,from_date,to_date,salary,hire_date
from employees e inner join salaries s on
s.from_date=e.hire_date and e.emp_no=1;
以上是分解开来的替代过程,最后用两个表的工资值相减,就是增长。
接下来写成连续的形式,而且要考虑是“所有员工的信息”,所以以emplouees表的emp_no为基准,先需要 employees 表和 salaries 表进行联结,通过 salaries.to_date ='9999-01-01'和employees.emp_no = salaries.emp_no,可以将此工资表重命名为一张表。这样的表的薪水就是当前薪水,然后再继续与薪水进行一次联结,(还是以emplouees表的emp no为基准)通过employee.hire_date = b.from_date和e.emp_no = b.emp_no,这个工资重命名为b表,这样b表的薪水就是入职时薪水,
代码如下:
1 2 3 4 5 6 7 8 9 10 |
|
这里可以很容易了解到:
a , b , e
当你需要做同一个表中数据做对比的时候,我们可以根据筛选的条件作为一个表去做joint