SQL24 - 获取所有非manager员工当前的薪水情况

题目描述：

获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date='9999-01-01'
 1 CREATE TABLE `dept_emp` (
 2 `emp_no` int(11) NOT NULL,
 3 `dept_no` char(4) NOT NULL,
 4 `from_date` date NOT NULL,
 5 `to_date` date NOT NULL,
 6 PRIMARY KEY (`emp_no`,`dept_no`));
 7 
 8 CREATE TABLE `dept_manager` (
 9 `dept_no` char(4) NOT NULL,
10 `emp_no` int(11) NOT NULL,
11 `from_date` date NOT NULL,
12 `to_date` date NOT NULL,
13 PRIMARY KEY (`emp_no`,`dept_no`));
14 
15 CREATE TABLE `employees` (
16 `emp_no` int(11) NOT NULL,
17 `birth_date` date NOT NULL,
18 `first_name` varchar(14) NOT NULL,
19 `last_name` varchar(16) NOT NULL,
20 `gender` char(1) NOT NULL,
21 `hire_date` date NOT NULL,
22 PRIMARY KEY (`emp_no`));
23 
24 CREATE TABLE `salaries` (
25 `emp_no` int(11) NOT NULL,
26 `salary` int(11) NOT NULL,
27 `from_date` date NOT NULL,
28 `to_date` date NOT NULL,
29 PRIMARY KEY (`emp_no`,`from_date`));

 

难易程度：

较难
 

解答：

1 select b.dept_no dept_no,a.emp_no emp_no, a.salary salary
2 from (
3 select emp_no,salary from salaries where emp_no not in (
4 select distinct emp_no from dept_manager where to_date='9999-01-01') and to_date='9999-01-01') a 
5 left join dept_emp b on a.emp_no = b.emp_no
6 where b.to_date='9999-01-01';

 

 

 

posted @ 2020-09-30 11:39  Python小白白  阅读(187)  评论(0)    收藏  举报