背景:当前数据库的department表、salgrade表、employee表,数据分别如下:
SQL语句:
SELECT t.*,s.grade FROM ( SELECT e.departmentid, d.`name`,AVG(salary) avgsal FROM employee e INNER JOIN department d on e.departmentid=d.id GROUP BY e.departmentid,d.`name` ) t INNER JOIN salgrade s ON t.avgsal BETWEEN s.losal AND s.hisal WHERE grade = (SELECT MIN(t.grade) mingra FROM ( SELECT t.*,s.grade FROM ( SELECT e.departmentid, d.`name`,AVG(salary) avgsal FROM employee e INNER JOIN department d on e.departmentid=d.id GROUP BY e.departmentid,d.`name`) t INNER JOIN salgrade s ON t.avgsal BETWEEN s.losal AND s.hisal) t )