3

题目：查找在当前时刻(to_date=‘9999-01-01’)，各个部门的经理的薪水详情以及其对应部门编号dept_no
题目隐含要求：最后输出的emp_no是按照从小到大的顺序排列的

方法1：纯where连接两表
select salaries.emp_no, salaries.salary, salaries.from_date, salaries.to_date, dept_manager.dept_no
from dept_manager, salaries
where dept_manager.emp_no=salaries.emp_no
and dept_manager.to_date='9999-01-01'
and salaries.to_date='9999-01-01'
order by salaries.emp_no;
注意：这里order by后面的emp_no前必须有salaries.或者dept_manager.
注意：因为存在换部门，甚至离职的情况，所以dept_manager.to_date='9999-01-01’是必须的
或：( salaries.emp_no, salaries.salary, salaries.from_date, salaries.to_date是salaries表的所有字段，所以可以用salaries.代替)
select salaries., dept_manager.dept_no
from dept_manager, salaries
where dept_manager.emp_no=salaries.emp_no
and dept_manager.to_date='9999-01-01'
and salaries.to_date='9999-01-01'
order by emp_no;
注意：因为使用了salaries.(其中包含emp_no)，所以order by后面的emp_no可以不加前缀
或：(给salaries和dept_manager起别名，下面提及它们的字段时，都可以简写)
select s., d.dept_no
from dept_manager d, salaries s
where d.emp_no=s.emp_no
and d.to_date='9999-01-01'
and s.to_date='9999-01-01'
order by emp_no;
补充：起别名的dept_manager as d中的as可以省略

方法2：用内连接
select s., d.dept_no
from dept_manager as d inner join salaries as s
on d.emp_no=s.emp_no
where d.to_date='9999-01-01'
and s.to_date='9999-01-01'
order by emp_no;
或
select s., d.dept_no
from salaries as s inner join dept_manager as d
on d.emp_no=s.emp_no
where d.to_date='9999-01-01'
and s.to_date='9999-01-01’;
补充：inner join中的inner可以省略，甚至inner join整个省略替换成，(逗号)也可以
注意：因为salaries表中emp_no是按照升序排列的，所以用salaries做主表就可以省略order by