获取员工其当前的薪水比其manager当前薪水还高的相关信息

SQL219. 获取员工其当前的薪水比其manager当前薪水还高的相关信息

emp_no	dept_no	from_date	to_date
10001	d001	1986-06-26	9999-01-01
10002	d001	1996-08-03	9999-01-01

有一个部门经理表dept_manager简况如下:

dept_no	emp_no	from_date	to_date
d001	10002	1996-08-03	9999-01-01

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

emp_no	salary	from_date	to_date
10001	88958	2002-06-22	9999-01-01
10002	72527	1996-08-03	9999-01-01

获取员工其当前的薪水比其manager当前薪水还高的相关信息，

第一列给出员工的emp_no，
第二列给出其manager的manager_no，
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary

以上例子输出如下:

emp_no	manager_no	emp_salary	manager_salary
10001	10002	88958	72527

示例1

输入:

drop table if exists  `dept_emp` ; 
drop table if exists  `dept_manager` ; 
drop table if exists  `salaries` ; 
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`));
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_manager VALUES('d001',10002,'1996-08-03','9999-01-01');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1996-08-03','9999-01-01');

输出:

10001|10002|88958|72527

原站题解

上次编辑到这里，代码来自缓存点击恢复默认模板

Sqlite 解法, 执行用时: 9ms, 内存消耗: 3584KB, 提交时间: 2021-08-08

select emp_sal.emp_no,mag_sal.manager_no,
emp_sal.emp_salary,mag_sal.manager_salary
from (
    select de.emp_no,de.dept_no,s1.salary as emp_salary
    from dept_emp de,salaries s1
    where de.emp_no=s1.emp_no
    and s1.to_date='9999-01-01'
    and de.to_date='9999-01-01'
)as emp_sal
inner join(
    select dm.emp_no as manager_no,dm.dept_no,s2.salary as manager_salary
    from dept_manager dm,salaries s2
    where dm.emp_no=s2.emp_no
    and s2.to_date='9999-01-01'
    and dm.to_date='9999-01-01'
)as mag_sal
on emp_sal.dept_no=mag_sal.dept_no
where mag_sal.manager_salary<emp_sal.emp_salary;

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3368KB, 提交时间: 2021-08-03

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 s.salary,s.emp_no,de.dept_no 
 from salaries s inner join dept_emp de 
 on s.emp_no=de.emp_no and s.to_date='9999-01-01')as s1,
 (select s.salary,s.emp_no,de.dept_no 
 from salaries s inner join dept_manager de 
 on s.emp_no=de.emp_no and s.to_date='9999-01-01')as s2
 where s1.dept_no = s2.dept_no and s1.salary>s2.salary

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3368KB, 提交时间: 2021-07-23

select t1.emp_no,t4.emp_no,t1.salary,t4.salary from 
(
    select a.emp_no,a.dept_no,t3.salary from dept_emp a 
join salaries t3 on a.emp_no=t3.emp_no --员工对应的工资表
) t1 --员工表

join (
    select a.emp_no,a.dept_no,b.salary from dept_manager a 
      join salaries b on a.emp_no=b.emp_no--对应的经理表 
) t4 on t1.dept_no=t4.dept_no
      where t1.salary>t4.salary;

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3388KB, 提交时间: 2022-01-03

with t1 as(select m.dept_no as dept_no,
m.emp_no as manager_no,s.salary as manager_salary
from salaries s
JOIN dept_manager m 
ON s.emp_no = m.emp_no),
t2 as (select d.dept_no as dept_no,
       d.emp_no as emp_no,s.salary as emp_salary
from salaries s
JOIN dept_emp d 
on d.emp_no = s.emp_no)

select t2.emp_no,t1.manager_no,t2.emp_salary,t1.manager_salary
from t1
join t2
on t1.dept_no = t2.dept_no
where t2.emp_salary > t1.manager_salary

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3480KB, 提交时间: 2021-08-09

SELECT dpe.emp_no,dp.emp_no AS manager_no,sa1.salary AS emp_salary,sa2.salary AS manager_salary   
FROM dept_emp dpe 
LEFT JOIN dept_manager dp ON dpe.dept_no=dp.dept_no 
LEFT JOIN salaries sa1 ON sa1.emp_no = dpe.emp_no     -- 匹配员工的
LEFT JOIN salaries sa2 ON sa2.emp_no = dp.emp_no       -- 匹配老板的
where  sa1.salary > sa2.salary;

上一题

下一题

详情