SELECT d1.name AS department
,e1.name AS employee
,e1.salary AS salary
,Max(e1.salary) OVER (PARTITION BY e1.departmentId) AS max_salary
FROM employee AS e1
INNER JOIN department AS d1 ON e1.departmentId = d1.id
WHERE salary = max_salary
여기까지 하고 막힌 이유는?
SELECT에서 연산한 결과물을 바로 WHERE 절에 쓸 수 없다!
...는 사실을 몰랐다.
위의 쿼리를 서브쿼리로 감싸주고 본쿼리에 WHERE 을 써줘야 함.
SELECT department
, employee
,max_salary
FROM (SELECT d1.name AS department
,e1.name AS employee
,e1.salary AS salary
,Max(e1.salary) OVER (PARTITION BY e1.departmentId) AS max_salary
FROM employee AS e1
INNER JOIN department AS d1 ON e1.departmentId = d1.id
)
WHERE salary = max_salary
여기서 또 막힘. 이유는?
서브쿼리에 AS로 이름설정 했어야 했는데, 그걸 몰랐다.
서브쿼리의 이름을 본쿼리의 SELECT와 WHERE 절에 써주는게 좋다.
예시)
SELECT 서브쿼리이름.컬럼이름
FROM ~~~~
WHERE 서브쿼리이름.컬럼이름
SELECT ms.department
, ms.employee
,ms.max_salary AS salary
FROM (SELECT d1.name AS department
,e1.name AS employee
,e1.salary AS salary
,Max(e1.salary) OVER (PARTITION BY e1.departmentId) AS max_salary
FROM employee AS e1
INNER JOIN department AS d1 ON e1.departmentId = d1.id
) AS ms
WHERE ms.salary = ms.max_salary