SQL197. 查找当前薪水详情以及部门编号dept_no

描述

示例1

输入:

drop table if exists  `salaries` ; 
drop table if exists  `dept_manager` ; 
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`));
INSERT INTO dept_manager VALUES('d001',10002,'9999-01-01');
INSERT INTO dept_manager VALUES('d002',10006,'9999-01-01');
INSERT INTO dept_manager VALUES('d003',10005,'9999-01-01');
INSERT INTO dept_manager VALUES('d004',10004,'9999-01-01');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01');
INSERT INTO salaries VALUES(10005,94692,'2001-09-09','9999-01-01');
INSERT INTO salaries VALUES(10006,43311,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10007,88070,'2002-02-07','9999-01-01');

输出:

10002|72527|2001-08-02|9999-01-01|d001
10004|74057|2001-11-27|9999-01-01|d004
10005|94692|2001-09-09|9999-01-01|d003
10006|43311|2001-08-02|9999-01-01|d002

原站题解

select s.emp_no,s.salary,s.from_date,s.to_date,dm.dept_no 
from salaries s join dept_manager dm on s.emp_no =dm.emp_no 
order by s.emp_no

SELECT s.emp_no, s.salary, s.from_date, s.to_date, d.dept_no
FROM salaries as s
join dept_manager as d on s.emp_no=d.emp_no
where d.to_date in ("9999-01-01","9999-01-01")
group by s.emp_no

select s.*,d.dept_no 
from dept_manager d inner join salaries s on  d.emp_no=s.emp_no
order by s.emp_no;

 select b.emp_no, b.salary, b.from_date,a.to_date,a.dept_no 
 from dept_manager a left join salaries b
 on a.emp_no=b.emp_no
 order by b.emp_no

select  b.*,a.dept_no from dept_manager a inner join salaries b on a.emp_no=b.emp_no order by b.emp_no