MySQL练习十五：查询员工的部门领导编号和自身的编号

drop table if exists  `dept_emp` ; 
drop table if exists  `dept_manager` ; 
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`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`));

INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d002','1995-12-03','9999-01-01');
INSERT INTO dept_manager VALUES('d001',10002,'1996-08-03','9999-01-01');
INSERT INTO dept_manager VALUES('d002',10003,'1990-08-05','9999-01-01');

# 推荐写法
SELECT a.emp_no, b.emp_no AS manager_no 
FROM dept_emp AS a INNER JOIN dept_manager AS b
ON a.dept_no = b.dept_no 
WHERE b.to_date = '9999-01-01' AND a.to_date = '9999-01-01' AND a.emp_no <> b.emp_no

# 个人写法。
SELECT e.emp_no, m.emp_no AS manager
FROM dept_emp e LEFT JOIN dept_manager m 
ON e.dept_no = m.dept_no
WHERE e.emp_no <> m.emp_no