30명인 학급의 학생들 월별 모의고사 성적을 1~3등까지만 월별로 쿼리하고 싶습니다.
help me~~
2020.11 1 김철수 98.7
2020.11 2 이영희 96.4
2020.11 3 하사랑 95.9
2020.10 1 김철수 99.2
2020.10 2 안일한 97.1
2020.10 3 하사랑 95.8
2020.09 1 김철수 99.9
2020.09 2 하사랑 96.4
2020.09 3 제임스 95.9
.
안녕하세요. 아래같이 해봤습니다. with 절에 샘플 데이터를 임의로 만들었습니다.
with t as (
select '2020.11' dt, '김철수' nm, 98.7 score from dual
union all select '2020.11', '이영희', 96.4 score from dual
union all select '2020.11', '하사랑', 95.9 score from dual
union all select '2020.11', '안일한', 94.1 score from dual
union all select '2020.11', '제임스', 94.2 score from dual
union all select '2020.10', '김철수', 99.2 score from dual
union all select '2020.10', '이영희', 94.1 score from dual
union all select '2020.10', '하사랑', 95.8 score from dual
union all select '2020.10', '안일한', 97.1 score from dual
union all select '2020.10', '제임스', 94.3 score from dual
union all select '2020.09', '김철수', 99.9 score from dual
union all select '2020.09', '이영희', 94.1 score from dual
union all select '2020.09', '하사랑', 96.4 score from dual
union all select '2020.09', '안일한', 94.2 score from dual
union all select '2020.09', '제임스', 95.9 score from dual
)
select *
from
(select dt, nm, score, row_number() over(partition by dt order by score desc) num from t)
where num <= 3
order by dt desc, num
정말 감사합니다 최고시네요~^^
동점자를 고려한다면.
ROW_NUMBER 보다는 RANK 를 쓰는 것이 좋을 듯 합니다.
동점인 경우를 고려하지 않았군요 ^^; 지적 감사드려요