[MySQL] HackerRank : The Report Solution

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

The Report | HackerRank

 

 

1. 코드 구현 

(1) 다중 조건 CASE WHEN

SELECT ( CASE WHEN Marks <= 69 THEN NULL ELSE Name END )as Name 
       ,( CASE WHEN Marks BETWEEN 10 AND 19 THEN 2
                WHEN Marks BETWEEN 20 AND 29 THEN 3
                WHEN Marks BETWEEN 30 AND 39 THEN 4
                WHEN Marks BETWEEN 40 AND 49 THEN 5
                WHEN Marks BETWEEN 50 AND 59 THEN 6
                WHEN Marks BETWEEN 60 AND 69 THEN 7
                WHEN Marks BETWEEN 70 AND 79 THEN 8
                WHEN Marks BETWEEN 80 AND 89 THEN 9
                WHEN Marks BETWEEN 90 AND 100 THEN 10
                ELSE 1
            END
          ) as Grade
        ,Marks 
FROM Students
ORDER BY Grade DESC, Name

 

(2) CTE 사용

WITH tmp AS(
SELECT Name 
     , (
          CASE WHEN Marks <= 9 THEN 1
               WHEN Marks <= 19 THEN 2
               WHEN Marks <= 29 THEN 3
               WHEN Marks <= 39 THEN 4
               WHEN Marks <= 49 THEN 5
               WHEN Marks <= 59 THEN 6
               WHEN Marks <= 69 THEN 7
               WHEN Marks <= 79 THEN 8
               WHEN Marks <= 89 THEN 9
               WHEN Marks <= 100 THEN 10
               ELSE NULL 
          END 
        )AS Grade
    , Marks
FROM Students
)

SELECT ( CASE WHEN Grade < 8 THEN NULL ELSE Name END )AS Name
       , tmp.Grade
       ,tmp.Marks
FROM tmp
ORDER BY tmp.Grade DESC, tmp.Name, tmp.Marks

 

2. 코드 개선

(1) IF 사용

# SELECT IF(G.Grade<8,NULL,S.Name) AS Name 도 가능 
SELECT IF(S.Marks<=69,NULL,S.Name) AS Name
       , G.Grade
       , S.Marks
FROM Students S 
INNER JOIN Grades G ON S.Marks BETWEEN G.Min_Mark AND G.Max_Mark
ORDER BY G.Grade DESC, S.Name

(2) CASE WHEN 사용

SELECT ( CASE WHEN G.Grade < 8 THEN NULL ELSE S.Name END )AS Name
       ,G.Grade
       ,S.Marks
FROM Grades G INNER JOIN Students S ON S.Marks BETWEEN G.Min_Mark AND g.Max_Mark
ORDER BY G.Grade DESC, S.Name, S.Marks

 

• Main Point)

join시 ON 절에 '=' 뿐만 아니라 'BETWEEN .. AND ..'도 사용할 수 있다.

Students 테이블의 Mark 값이 Grades 테이블의 Min_Mark와 Max_Mark 사이에 있을 경우,

INNER JOIN을 통해 하나의 튜플로 묶여 테이블이 형성된다.