SQL | 多表连接 | 薪水涨幅

一、查找当前所有雇员入职以来的薪水涨幅，给出雇员编号以及其对应的薪水涨幅，并按照薪水涨幅进行升序。

（注：薪水表中结束日期为2014-5-1的才是当前员工，否则是已离职员工）
1、创建表

# 表employee
CREATE TABLE IF NOT EXISTS employee(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
birthday DATE,
name VARCHAR(10),
sex VARCHAR(10),
hiredate DATE
);

# 表salary
CREATE TABLE IF NOT EXISTS salary(
emp_id INT,
salary INT,
startdate DATE,
enddate DATE,
CONSTRAINT FOREIGN KEY(emp_id) REFERENCES employee(emp_id)
);

2、插入数据

INSERT INTO employee VALUES(1,'1988-5-7','小明','男','2003-4-1'),
						   (2,'1989-7-7','小红','女','2008-9-9'),
						   (3,'1990-8-18','小兰','女','2006-5-1'),
						   (4,'1994-8-7','小飞','男','2010-3-17');

INSERT INTO salary VALUES(1,7680,'2003-4-1','2005-4-1'),
						 (1,12000,'2005-4-1','2008-4-1'),
						 (1,14560,'2008-4-1','2014-5-1'),
						 (2,8000,'2008-9-9','2010-5-1'),
						 (3,9000,'2006-5-1','2008-5-1'),
						 (3,11800,'2008-5-1','2014-5-1'),
						 (4,13000,'2010-3-17','2014-5-1');

3、逻辑SQL
（1）求出还在职人员的当前薪水

mysql> SELECT emp_id,salary AS endsalary
    -> FROM salary
    -> WHERE enddate='2014-5-1';
+--------+-----------+
| emp_id | endsalary |
+--------+-----------+
|      1 |     14560 |
|      3 |     11800 |
|      4 |     13000 |
+--------+-----------+
3 rows in set (0.00 sec)

（2）求出人员的起始薪水
两表连接，当startsalary=hiredate时，薪水为起始薪水
因后面考虑到两表做内连接，此步骤WHERE enddate='2014-5-1’可加可不加

mysql> SELECT e.emp_id,e.name,s.salary AS startsalary
    ->  FROM employee e LEFT JOIN salary s
    ->  ON e.emp_id=s.emp_id
    ->  AND e.hiredate=s.startdate;
+--------+--------+-------------+
| emp_id | name   | startsalary |
+--------+--------+-------------+
|      1 | 小明   |        7680 |
|      2 | 小红   |        8000 |
|      3 | 小兰   |        9000 |
|      4 | 小飞   |       13000 |
+--------+--------+-------------+
4 rows in set (0.00 sec)

（3）一二步骤的中间表内连接，求薪水差

mysql> SELECT t1.emp_id,t1.name,(t2.endsalary-t1.startsalary) AS '薪水涨幅'
    -> FROM (SELECT e.emp_id,e.name,s.salary AS startsalary
    ->  	FROM employee e LEFT JOIN salary s
    ->  	ON e.emp_id=s.emp_id
    ->  	AND e.hiredate=s.startdate) t1
    -> INNER JOIN
    ->  	(SELECT emp_id,salary AS endsalary
    ->  	FROM salary
    ->  	WHERE enddate='2014-5-1') t2
    -> ON t1.emp_id=t2.emp_id;
+--------+--------+--------------+
| emp_id | name   | 薪水涨幅     |
+--------+--------+--------------+
|      1 | 小明   |         6880 |
|      3 | 小兰   |         2800 |
|      4 | 小飞   |            0 |
+--------+--------+--------------+
3 rows in set (0.00 sec)