题目描述
获取员工其当前的薪水比其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
sem.emp_no as emp_no,
sdm.emp_no as manager_no,
sem.salary as emp_salary,
sdm.salary as manager_salary
from (select
s.salary,
s.emp_no,
de.dept_no
from salaries s
join dept_emp de on s.emp_no = de.emp_no and s.to_date = '9999-01-01'
) sem,
(select
s.salary,
s.emp_no,
dm.dept_no
from salaries s
join dept_manager dm on s.emp_no = dm.emp_no and s.to_date = '9999-01-01'
) sdm
where sem.dept_no = sdm.dept_no and sem.salary > sdm.salary