SQL3查找各个部门当前领导当前薪水详情以及其对应部门编号dept

题目描述

查找各个部门当前(dept_manager.to_date='9999-01-01')领导当前(salaries.to_date='9999-01-01')薪水详情以及其对应部门编号dept_no

(注:输出结果以salaries.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`));

CREATE TABLE `dept_manager` ( `dept_no` char(4) NOT NULL, -- '部门编号' `emp_no` int(11) NOT NULL, --  '员工编号' `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`dept_no`));  

输入描述:

无

输出描述:

emp_nosalaryfrom_dateto_datedept_no10002725272001-08-029999-01-01d00110004740572001-11-279999-01-01d00410005946922001-09-099999-01-01d00310006433112001-08-029999-01-01d00210010944092001-11-239999-01-01d006

 

SELECT S.emp_no,S.salary,S.from_date,S.to_date,DM.dept_no FROM salaries S INNER JOIN dept_manager DM ON S.emp_no=DM.emp_no WHERE S.to_date='9999-01-01' AND DM.to_date='9999-01-01';