方法一:联结两个表,然后内嵌一个子查询用于查询最高工资,再将子查询与外面的联结用部门联系在一起,即可查找出各部门工资最高情况
SELECT de.dept_no, de.emp_no, s.salary maxSalary FROM dept_emp de JOIN salaries s ON de.emp_no = s.emp_no WHERE s.salary IN (SELECT MAX(s1.salary) FROM dept_emp de1 JOIN salaries s1 ON de1.emp_no = s1.emp_no AND de1.dept_no = de.dept_no) ORDER BY de.dept_no
方法二:制作两张表,一张是部门、员工、工资表,另一张是部门、最高工资表,然后将两表联结在一起,查询出结果
SELECT t1.dept_no, t1.emp_no, t2.salary FROM (SELECT de1.dept_no, s1.emp_no, s1.salary FROM dept_emp de1 join salaries s1 ON de1.emp_no = s1.emp_no ) AS t1, (SELECT de2.dept_no, max(s2.salary) as salary FROM dept_emp de2 join salaries s2 ON de2.emp_no = s2.emp_no GROUP BY de2.dept_no ) AS t2 WHERE t1.salary = t2.salary AND t1.dept_no = t2.dept_no ORDER BY t1.dept_no;
方法三:这个我也不算确定,但是在SQLite里是能通过的,刚学的一点点开窗函数在这题上的使用,其实思路和方法一类似,如果不对欢迎指出
SELECT de.dept_no, de.emp_no, s.salary FROM dept_emp de JOIN salaries s ON de.emp_no = s.emp_no WHERE s.salary IN (SELECT MAX(s1.salary) OVER(PARTITION BY de1.dept_no) FROM dept_emp de1 JOIN salaries s1 ON de1.emp_no = s1.emp_no WHERE de.dept_no = de1.dept_no) ORDER BY de.dept_no