问题描述
获取所有部门当前(dept_manager.to_date=‘9999-01-01’)manager的当前(salaries.to_date=‘9999-01-01’)薪水情况,给出dept_no, emp_no以及salary(请注意,同一个人可能有多条薪水情况记录)
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
));
来源:牛客网
链接:https://www.nowcoder.com/practice/4c8b4a10ca5b44189e411107e1d8bec1?tpId=82&tqId=29761&rp=0&ru=/ta/sql&qru=/ta/sql/question-ranking
Sql语句
select a.dept_no, a.emp_no, b.salary
from dept_manager as a, salaries as b
where a.emp_no = b. emp_no
and a.to_date = '9999-01-01'
and b.to_date = '9999-01-01';