조인 조건이 특이한 문제 풀이

The Report [Hackerrank]

https://www.hackerrank.com/challenges/the-report/problem

JOIN 조건에 BETWEEN을 쓸 수 있다.

select case when g.grade < 8 then null else s.name end as name
        , g.grade
        , s.marks
from students as s
    inner join grades as g on s.marks between g.min_mark and g.max_mark
order by g.grade desc, s.name asc, s.marks asc

 

180. Consecutive-Number

https://leetcode.com/problems/consecutive-numbers/

다중 셀프 조인하기.

select distinct l.num as ConsecutiveNums
from logs as l
    inner join logs as l_next on l.id + 1 = l_next.id
    inner join logs as l_next2 on l.id + 2 = l_next2.id
where l.num = l_next.num and l.num = l_next2.num

+----+-----+ -> +----+-----+-----+
| id | num |    | id | num | num |
+----+-----+    +----+-----+-----+
| 1  | 1   |    | 1  | 1   | 1   |
| 2  | 1   |    | 2  | 1   | 1   |
| 3  | 1   |    | 3  | 1   | 2   |
| 4  | 2   |    | 4  | 2   | 1   |
| 5  | 1   |    | 5  | 1   | 2   |
| 6  | 2   |    | 6  | 2   | 2   |
| 7  | 2   |    | 7  | 2   |     |
+----+-----+    +----+-----+-----+