Skip to main content

[SQL] 284527 조건에 맞는 사원 정보 조회하기

https://school.programmers.co.kr/learn/courses/30/lessons/284527

image.png

image.png

image.png

image.png

📌문제

3개의 테이블(HR_DEPARTMENT, HR_EMPLOYEES, HR_GRADE)을 조인해서
2022년 한 해(상·하반기) 평가 점수가 가장 높은 사원의 정보를 구하기

  • 2022년 데이터만 조회 (WHERE g.year = 2022)
  • 상·하반기 점수를 합산해서 SCORE 컬럼으로 표시
  • 최고 점수를 받은 사원만 조회
  • 출력 컬럼: SCORE, EMP_NO, EMP_NAME, POSITION, EMAIL
  • 집계 함수(SUM) 사용 시 GROUP BY에 나머지 컬럼들 포함

💡 정답쿼리

CTE 

WITH total_scores AS (
    SELECT
        e.emp_no,
        e.emp_name,
        e.position,
        e.email,
        SUM(g.score) AS score
    FROM hr_employees e
    JOIN hr_department d
        ON e.dept_id = d.dept_id
    JOIN hr_grade g
        ON e.emp_no = g.emp_no
    WHERE g.year = 2022
    GROUP BY e.emp_no, e.emp_name, e.position, e.email
)
SELECT
    score,
    emp_no,
    emp_name,
    position,
    email
FROM total_scores
WHERE score = (SELECT MAX(score) FROM total_scores);

WITH total_scores AS (...)

  • 먼저 2022년 평가 데이터(g.year = 2022)만 필터링
  • GROUP BY로 사번별 점수 합계(SUM) 계산
  • 이 합계를 컬럼명 score로 저장하기

메인 쿼리

  • total_scores CTE에서 score가 최대값인 사원만 선택
  • 동점자가 여러 명이면 모두 출력됨

주의사항

SUM(g.score)처럼 집계함수를 쓰면, 그 외에 SELECT 절에 나오는 모든 일반 컬럼(집계함수에 안 들어간 컬럼)은 GROUP BY 안에 반드시 있어야 한다. 내가 쓴 쿼리에서는

SELECT e.emp_no, e.emp_name, e.position, e.email, SUM(g.score)

이렇게 쓰고 있으니까

  • emp_no
  • emp_name
  • position
  • email

이 네 개가 전부 GROUP BY 절에 들어가야 한다.

💡 "그럼 왜 emp_no만 쓰면 안 되는가?

emp_no는 사원 식별자라 사실상 유니크한 값이지만, SQL 표준에서는 명시적으로 GROUP BY에 나머지 컬럼을 다 넣는 걸 요구한다. MySQL은 ONLY_FULL_GROUP_BY 모드가 꺼져 있으면 emp_no만 넣어도 에러 없이 동작하긴 하지만, 표준 SQL이나 안전한 모드에서는 에러가 난다.

단, 만약 emp_no가 PK(기본키)라면, emp_no만 GROUP BY에 넣고 나머지 컬럼은 MAX()나 MIN() 같은 집계함수로 감싸도 결과는 동일하게 만들 수 있다.

예시:

SELECT 
    e.emp_no,
    MAX(e.emp_name) AS emp_name,
    MAX(e.position) AS position,
    MAX(e.email) AS email,
    SUM(g.score) AS score
FROM ...
GROUP BY e.emp_no;

이렇게 하면 GROUP BY에 한 개만 써도 표준에 맞게 돌아간다.

개선한 쿼리

필자는 CTE 로 풀었는데 이걸 윈도우 함수(ROW_NUMBER() OVER (...))로 깔끔하게 바꾸면 HAVING 없이도 한 번에 "상/하반기별 최고 점수 직원"만 뽑을 수 있다. FROM절에 ROW_NUMBER() 서브쿼리를 써서 푸는 방법이다.

SELECT score,
       emp_no,
       emp_name,
       position,
       email
FROM (
    SELECT e.emp_no,
           e.emp_name,
           e.position,
           e.email,
           SUM(g.score) AS score,
           ROW_NUMBER() OVER (ORDER BY SUM(g.score) DESC) AS rn
    FROM hr_employees e
    JOIN hr_grade g
        ON e.emp_no = g.emp_no
    WHERE g.year = 2022
    GROUP BY e.emp_no, e.emp_name, e.position, e.email
) ranked
WHERE rn = 1;
  1. SUM(g.score) → 사원별 2022년 총점 계산하기
  2. ROW_NUMBER() OVER (ORDER BY SUM(...) DESC) → 점수 높은 순서로 순위 매기기
  3. rn = 1만 필터링 → 최고 점수 사원만 조회하기

이렇게 하면 HAVING MAX()나 서브쿼리 없이 한 번에 필터링 가능하다.
동점자도 RANK()나 DENSE_RANK()로 쉽게 처리 할 수있다.


Back to top