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

SELECT TB.emp_no, 
        MAX(TB.salary)-MIN(TB.salary) AS growth
FROM employees TA
LEFT JOIN salaries TB
ON TA.emp_no = TB.emp_no 
    AND (TA.hire_date = TB.from_date 
            OR TB.to_date = '9999-01-01')
GROUP BY TB.emp_no
HAVING SUM(CASE WHEN TB.to_date = '9999-01-01' THEN 1 ELSE 0 END) > 0
ORDER BY growth