题解 | 查找在职员工自入职以来的薪水涨幅情况

SELECT min_salaries.`emp_no`,
COALESCE(max_salaries.`salary`, 0) - COALESCE(min_salaries.`salary`, 0) growth
FROM
(SELECT `emp_no`,`salary` FROM `salaries`
    WHERE `from_date` in 
        (SELECT e.`hire_date` FROM `employees` e
            WHERE e.`emp_no` IN 
            (SELECT `salaries`.`emp_no` FROM `salaries` 
            WHERE `to_date` = '9999-01-01'))) min_salaries
JOIN 
(SELECT `emp_no`,`salary` FROM `salaries`
    WHERE `to_date` = '9999-01-01') max_salaries
ON min_salaries.`emp_no` = max_salaries.`emp_no`
ORDER BY growth asc;

累死我了，只会写subquery