SQL-3-查找当前薪水详情以及部门编号dept_no

题目描述

查找各个部门当前(to_date='9999-01-01')领导当前薪水详情以及其对应部门编号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`));

组合主键：虽然每张表只能有一个主键，这种形式看起来是多个，其实表达的是一个

错误答案：select dept_manager.emp_no,salaries.salary,salaries.from_date,salaries.to_date,dept_manager.dept_no from dept_manager,salaries where salaries.emp_no in (select emp_no from dept_manager where to_date='9999-01-01') AND salaries.emp_no=dept_manager.emp_no; 

错误答案：select dept_manager.emp_no,salaries.salary,salaries.from_date,salaries.to_date,dept_manager.dept_no from dept_manager,salaries where salaries.emp_no=dept_manager.emp_no AND salaries.emp_no in (select emp_no from dept_manager where to_date='9999-01-01') ; 

错误答案：select s.*,d.dept_no from dept_manager d,salaries s where s.emp_no=d.emp_no AND s.to_date='9999-01-01' AND d.to_date='9999-01-01'; 

上面换了顺序也不可以通过

以上答案留待后面分析

正确答案：select s.*,d.dept_no from salaries s, dept_manager d where s.emp_no=d.emp_no AND s.to_date='9999-01-01' AND d.to_date='9999-01-01'; 

正确答案：select salaries.emp_no,salaries.salary,salaries.from_date,salaries.to_date,dept_manager.dept_no from salaries inner join dept_manager on dept_manager.emp_no = salaries.emp_no and dept_manager.to_date = '9999-01-01' and salaries.to_date = '9999-01-01';