题解 | 统计各个部门平均薪资

SELECT 
    department, AVG(pay_salary) as avg_salary
FROM
    (
        SELECT 
            stt.department as department,
            (sat.normal_salary - sat.dock_salary) as pay_salary
        FROM
            staff_tb as stt
            LEFT JOIN salary_tb as sat ON stt.staff_id = sat.staff_id
        WHERE
            (sat.normal_salary - sat.dock_salary) BETWEEN 4000 AND 30000
        GROUP BY
            department,
            pay_salary
    ) as re
GROUP BY department
ORDER BY avg_salary DESC

# SQL_ERROR_INFO Not Select，总是遇到这个错误，记录一下，是由于牛客自动代码格式化的原因。在SELECT后面加一个空格就行