题解 | 用with定义两个子句，简化sql语法结构

WITH  #with子句只能出现一次
    es AS (
        SELECT
            d.*,
            s.salary
        FROM
            dept_emp d
            LEFT JOIN salaries s ON d.emp_no = s.emp_no
    ),  #with下的两个子句用逗号连接
    ms AS (
        SELECT
            m.*,
            s.salary
        FROM
            dept_manager m
            JOIN salaries s ON m.emp_no = s.emp_no
    )  #with与select间不需要其他符号
SELECT
    es.emp_no,
    ms.emp_no AS manager_no,
    es.salary AS emp_salary,
    ms.salary AS manager_salary
FROM
    es
JOIN
    ms ON es.dept_no = ms.dept_no
WHERE
    es.salary > ms.salary;