题解 | #获取每个部门中当前员工薪水最高的相关信息#

Step1: it is straightforward to use INNER JOIN to collect all required info from given tables. So we could create one table just containing the results columns using Inner JOIN

Step2. we know that once GROUP BY is used then all columns that appear in the SELECT clause only have 3 alternatives: columns that appear in GROUP BY; CONSTANT; AGG functions like MAX() in this case. SO if we want to get the final result with three columns. The first column dept_no is easy to get since it appears in GROUB BY, max salary is available as well since it appears in AGG function. But emp_no cannot be selected in the SELECT clause since it is not in any of the 3 alternatives. Then we could create another table where we use GROUP BY to get the highest salary per department.

step3: SELECT all expected columns in order from two tables. syntax is:

SELECT * FROM 
(SELECT * FROM ....) AS table1 # alias 
(SELECT * FROM ....) AS table2 # alias 
WHERE ... # condition
ORDER BY ...; 

Final codes:

SELECT table1.dept_no, table1.emp_no, table2.maxSalary FROM 

(SELECT dept_no, dept_emp.emp_no, salary FROM dept_emp 
INNER JOIN salaries 
ON dept_emp.emp_no=salaries.emp_no) AS table1,

(SELECT dept_emp.dept_no, MAX(salary) AS maxSalary FROM dept_emp
INNER JOIN salaries
ON dept_emp.emp_no=salaries.emp_no
GROUP BY dept_emp.dept_no) AS table2

WHERE table1.dept_no=table2.dept_no AND table1.salary=table2.maxSalary
ORDER BY table1.dept_no ASC;