[SQL] 서브쿼리

프로그래머스 - 조건에 맞는 사원 정보 조회하기

문제를 처음에는

SELECT SUM(SCORE) AS SCORE, EMP_NO, EMP_NAME, POSITION, EMAIL
FROM HR_EMPLOYEES
  JOIN HR_DEPARTMENT USING(DEPT_ID) 
  JOIN HR_GRADE USING(EMP_NO)
GROUP BY EMP_NO
ORDER BY SUM(SCORE) DESC LIMIT 1

이렇게 풀었는데 최고점수의 사원이 한 명이 아니라 여러명일 경우를 생각해서 LIMIT를 사용하지 않고 다시 풀어보기로 했다.

-- 집계함수(SUM, MAX, MIN...)은 동일쿼리 내에서 중첩할 수 없다.
SELECT MAX(SUM(SCORE)), EMP_NO, EMP_NAME, POSITION, EMAIL
FROM HR_EMPLOYEES
  JOIN HR_DEPARTMENT USING(DEPT_ID) 
  JOIN HR_GRADE USING(EMP_NO)
GROUP BY EMP_NO
-- 대신 다음 처럼 서브쿼리를 이용해야 함.
SELECT MAX(SCORE), EMP_NO, EMP_NAME, POSITION, EMAIL
FROM (SELECT SUM(SCORE) AS SCORE, EMP_NO, EMP_NAME, POSITION, EMAIL  FROM HR_EMPLOYEES
  JOIN HR_DEPARTMENT USING(DEPT_ID) 
  JOIN HR_GRADE USING(EMP_NO)
GROUP BY EMP_NO)
-- 근데 ORACLE과 달리 MYSQL은 서브쿼리에 ALIAS 지정안해주면 에러남.
-- 실패 (1248, 'Every derived table must have its own alias')
SELECT MAX(SCORE), EMP_NO, EMP_NAME, POSITION, EMAIL
FROM (SELECT SUM(SCORE) AS SCORE, EMP_NO, EMP_NAME, POSITION, EMAIL  FROM HR_EMPLOYEES
  JOIN HR_DEPARTMENT USING(DEPT_ID) 
  JOIN HR_GRADE USING(EMP_NO)
GROUP BY EMP_NO) A
-- 또 에러발생, Group By 절에 포함되지 않는 Column (nonaggregated column)을 select 할 경우, 컬럼의 어느 부분에 표시해야 할 지 애매하여 발생하는 에러입니다. (Exception)
-- 실패 (1140, "In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'A.EMP_NO'; this is incompatible with sql_mode=only_full_group_by")
SELECT MAX(SCORE), EMP_NO, EMP_NAME, POSITION, EMAIL
FROM (SELECT SUM(SCORE) AS SCORE, EMP_NO, EMP_NAME, POSITION, EMAIL FROM HR_EMPLOYEES
  JOIN HR_DEPARTMENT USING(DEPT_ID) 
  JOIN HR_GRADE USING(EMP_NO)
GROUP BY EMP_NO) A
-- 다음 처럼하면 에러는 고치지만 가장 점수가 큰 김연주 사원만이 아니라 모든 사원의 정보가 나옴.
SELECT MAX(SCORE), EMP_NO, EMP_NAME, POSITION, EMAIL
FROM (SELECT SUM(SCORE) AS SCORE, EMP_NO, EMP_NAME, POSITION, EMAIL FROM HR_EMPLOYEES
  JOIN HR_DEPARTMENT USING(DEPT_ID) 
  JOIN HR_GRADE USING(EMP_NO)
GROUP BY EMP_NO) A
GROUP BY EMP_NO
-- 그렇다고 다음처럼하면 SCORE 컬럼만 나오게 됨.
SELECT MAX(SCORE)
FROM (SELECT SUM(SCORE) AS SCORE, EMP_NO, EMP_NAME, POSITION, EMAIL FROM HR_EMPLOYEES
  JOIN HR_DEPARTMENT USING(DEPT_ID) 
  JOIN HR_GRADE USING(EMP_NO)
GROUP BY EMP_NO) A
-- 기존 코드로는 불가능할거같아서 다시 쿼리를 새로 짬.
-- 다음 코드는 성공!
SELECT * FROM (SELECT SUM(SCORE) AS SCORE, EMP_NO, EMP_NAME, POSITION, EMAIL FROM HR_EMPLOYEES
  JOIN HR_DEPARTMENT USING(DEPT_ID) 
  JOIN HR_GRADE USING(EMP_NO)
GROUP BY EMP_NO) A
WHERE SCORE = (SELECT MAX(SCORE) FROM (SELECT SUM(SCORE) AS SCORE, EMP_NO, EMP_NAME, POSITION, EMAIL FROM HR_EMPLOYEES
  JOIN HR_DEPARTMENT USING(DEPT_ID) 
  JOIN HR_GRADE USING(EMP_NO)
GROUP BY EMP_NO) B)