SQL多表减法

题目描述

有一个员工表employees简况如下:

image

有一个薪水表salaries简况如下:

image

请你查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序，以上例子输出为

（注:可能有employees表和salaries表里存在记录的员工，有对应的员工编号和涨薪记录，但是已经离职了，离职的员工salaries表的最新的to_date!='9999-01-01'，这样的数据不显示在查找结果里面，以上emp_no为2的就是这样的）

image

1、使用内连接获取员工入职薪水

select s.emp_no,s.salary

from salaries as s

inner join employees as e

on s.emp_no = e.emp_no and s.from_date=e.hire_date

image.png

2、使用俩次内连接获取员工入职薪水和当前薪水

select s1.emp_no,s1.salary

from salaries s1 inner join

(select s.emp_no,s.salary

from salaries as s

inner join employees as e

on s.emp_no = e.emp_no and s.from_date=e.hire_date) as e1

on s1.emp_no=e1.emp_no

image.png

3、结果做差

select s1.emp_no,(s1.salary-e1.salary) as growth

from

# 查询当前工资

(select emp_no,salary

from salaries

where to_date='9999-01-01') as s1

# 内连接2次查询结果

inner join

# 查询入职时候的工资

(select s.emp_no,s.salary

from salaries as s

inner join employees as e

on s.emp_no = e.emp_no and s.from_date=e.hire_date) as e1

on s1.emp_no=e1.emp_no

order by growth

扩展：
需求：
查询 C 的结果，其中 C = A - B;
A的结果需要连表查询；
B的结果也需要连表查询；

实现：
过程一：

select sum(A) from 表a a
left join 表b b
on a.pid = b.id
where b.id = 1;

过程二：

select sum(B) from 表c c
left join 表d d
on c.pid = d.id
where d.id = 1;

结果：

select(m.A - n.B) as result
from
(select sum(A) as A from 表a a
left join 表b b
on a.pid = b.id
where a.id = 1) m,
(select sum(B) as B from 表c c
left join 表d d
on c.pid = d.id
where d.id = 1)n;