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

1查员工最开始工资，to_date字段
2查员工最后工资
3两工资关联到一张表，计算涨幅
4过滤在职员工

SELECT
    c.emp_no,
    c.salary - d.salary growth
FROM
    (
        SELECT
            a.emp_no,
            a.to_date,
            a.salary
        FROM
            salaries a
        INNER JOIN (
            SELECT
                emp_no,
                max(to_date) maxdate
            FROM
                salaries
            GROUP BY
                emp_no
        ) b ON a.emp_no = b.emp_no
        AND a.to_date = b.maxdate
    ) c,
    (
        SELECT
            a.emp_no,
            a.to_date,
            a.salary
        FROM
            salaries a
        INNER JOIN (
            SELECT
                emp_no,
                min(to_date) mindate
            FROM
                salaries
            GROUP BY
                emp_no
        ) b ON a.emp_no = b.emp_no
        AND a.to_date = b.mindate
    ) d
WHERE
    c.emp_no = d.emp_no
AND DATE_FORMAT(c.to_date,'%Y-%m-%d')='9999-01-01'
ORDER BY
    growth ASC;