Topic describes

There is a salary table for all employees as follows:

Dept_manager = dept_manager = dept_manager

Please find the salary details of the current leaders of each department and their corresponding department number dept_no. The output result is in ascending order as salaries. Emp_no, and please note that dept_no is the last column in the output result.

Example 1 Enter: 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 (58, 10001889 '2002-06-22', '9999-01-01'); INSERT INTO salaries VALUES (27, 10002725, '2001-08-02', '9999-01-01'); INSERT INTO salaries VALUES (11, 10003433, '2001-12-01', '9999-01-01'); INSERT INTO salaries VALUES (10004740, 57, '2001-11-27', '9999-01-01'); INSERT INTO salaries VALUES (10005946, 92, '2001-09-09', '9999-01-01'); INSERT INTO salaries VALUES (11, 10006433, '2001-08-02', '9999-01-01'); INSERT INTO salaries VALUES (10007880, 70, '2002-02-07', '9999-01-01'); Copy output:  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|d002Copy the code

Answer key

ORDER BY:

Elapsed time: 13ms over 85.06% Code submitted with Sqlite Memory: 3408KB Over 37.17% code submitted with Sqlite

SELECT a.*, b.dept_no 
FROM salaries a JOIN dept_manager b
WHERE b.emp_no IN (SELECT a.emp_no FROM salaries)
ORDER BY a.emp_no ASC; 
Copy the code

Dept_manager. to_date=’9999-01-01 ‘is required:

Elapsed time: 11ms over 99.43% Code submitted with Sqlite memory: 3448KB Over 10.85% code submitted with Sqlite

SELECT a.*, b.dept_no 
FROM salaries a JOIN dept_manager b
WHERE b.emp_no IN (SELECT a.emp_no FROM salaries)
AND a.to_date = '9999-01-01' 
AND b.to_date = '9999-01-01'
ORDER BY a.emp_no ASC; 
Copy the code

To simplify:

Elapsed time: 13ms over 85.06% Code submitted with Sqlite Memory: 4324KB Over 3.75% code submitted with Sqlite

SELECT a.*, b.dept_no 
FROM salaries a JOIN dept_manager b
ON b.emp_no = a.emp_no
WHERE a.to_date = '9999-01-01' 
AND b.to_date = '9999-01-01'
ORDER BY a.emp_no ASC; 
Copy the code