MySQL 해커랭크 | The Report (서브쿼리 내 메인쿼리 컬럼 참조)

 

문제

https://www.hackerrank.com/challenges/the-report/problem?isFullScreen=true

The Report | HackerRank

• The report must be in descending order by grade -- i.e. higher grades are entered first.
• If there is more than one student with the same grade (1-7) assigned to them, order those particular students by their marks in ascending order.
• If there is more than one student with the same grade (8-10) assigned to them, order those particular students by their name alphabetically.
• Finally, if the grade is lower than 8, use "NULL" as their name and list them by their grades in descending order.

 

테이블

Students 테이블	Grades 테이블

• Grades 테이블을 보면 점수가 높을수록 등급도 10에 가까워짐
• 1등급이 제일 높은 등급이라는 고정관념으로 풀었더니 계속 틀렸었음..ㅋㅋ

 

풀이과정

# 각 학생들의 등급 Grade 확인하기

• 특이사항 
  Students 테이블의 Min_Mark와 Max_Mark를
  SELECT문의 서브쿼리문 내에서 참조하고 있음 (..이게 되네..?)
  • 메인 테이블의 컬럼을, 서브쿼리 내부 테이블의 컬럼과 함께 비교하고 있음

SELECT Name, 
      (SELECT Grade FROM Grades WHERE Marks >= Min_Mark  AND Marks <= Max_Mark) Grade, 
       Marks
FROM Students

 

# 정답

• 뭔가.. 허술하게 유야무야 정답을 맞춘 느낌..

SELECT IF(Grade BETWEEN 8 AND 10, Name, NULL) Name, 
       Grade, 
       Marks
FROM  (SELECT Name,
             (SELECT Grade FROM Grades WHERE Marks >= Min_Mark AND Marks <= Max_Mark) Grade,
              Marks 
              FROM Students) a
ORDER BY Grade DESC, Name