题目: 获取员工其当前的薪水比其manager当前薪水还高的相关信息,当前表示to_date=‘9999-01-01’,
结果第一列给出员工的emp_no,
第二列给出其manager的manager_no,
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary
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 dept_manager
(
dept_no
char(4) NOT NULL,
emp_no
int(11) NOT NULL,
from_date
date NOT NULL,
to_date
date NOT NULL,
PRIMARY KEY (emp_no
,dept_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
));
select s1.emp_no as emp_no,s2.emp_no as manager_no,s1.salary as emp_salary, s2.salary as manager_salary
from (select e.emp_no,e.dept_no,s.salary
from dept_emp e
join salaries s on e.emp_no=s.emp_no
where e.to_date='9999-01-01' and s.to_date='9999-01-01') as s1
join (select m.emp_no,m.dept_no,s.salary,m.dept_no
from dept_manager m
join salaries s on m.emp_no=s.emp_no
where m.to_date='9999-01-01' and s.to_date='9999-01-01') as s2
on s1.dept_no = s2.dept_no
where s1.salary>s2.salary;
思路:
创建两张表:
一张表记录所有员工的当前工资 s1
另一张记录经理的当前工资 s2
s1: 连接salaries和demp_emp,限制to_date, 记录所有员工的当前工资
s2: 连接salaries和demp_manager,限制to_date, 记录经理的当前工资
员工的dept_no和自己部门经理的dept_no一样
用s1.dept_no= s2.dept_no 连接s1 s2两张表
限制条件s1.salary>s2.salary 查询员工比自己经理工资高的记录
注意:同一张表里的列与列之间可以比大小