数据库SQL实战- 获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date='9999-01-01'...

题目描述
获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date='9999-01-01'
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 employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_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));

解题思路：

先查出所有manager的薪水情况 ，然后筛选出emp_no 不在dept_manager 里面的就行了

SQL:

select d.dept_no,e.emp_no,s.salary
from dept_emp as d,
(select emp_no from employees where emp_no not in (select emp_no from dept_manager)) as e,salaries as s
where d.emp_no = e.emp_no
and d.emp_no = s.emp_no
and d.to_date='9999-01-01'
and s.to_date='9999-01-01'

转载于:https://www.cnblogs.com/alexhere/p/10386603.html