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

 

 

 

WTIH SUM_SCORE AS (
	SELECT EMP_NO
    	,SUM(SCORE) AS SCORE
    FROM HR_GRADE
    GROUP BY EMP_NO
), RANKING AS (
	SELECT EMP_NO
    	, SCORE
        , DENSE_RANK() OVER (ORDER BY SCORE DESC) AS DR
    FROM SUM_SCORE
)

SELECT RK.SCORE
	, RK.EMP_NO
    , HE.EMP_NAME
    , HE.POSITION
    , HE.EMAIL
FROM HR_EMPLOYEES HE
	INNER JOIN RANKING RK ON HE.EMP_NO = RK.EMP_NO
WHERE RK.DR = 1;