HackerRank SQL(Medium) 解法 | Occupations

問題

www.hackerrank.com

解法(MySQL)

• occupationのグループの最大数分の連番を作り，連番にそれぞれLEFT OUTER JOINする．

with doctors as (
    select
        name,
        @d_i := @d_i+1 as r
    from
        occupations,
        (select @d_i := 0) as tmp
    where
        occupation = 'Doctor'
    order by
        name asc
),
professors as (
    select
        name,
        @p_i := @p_i+1 as r
    from
        occupations,
        (select @p_i := 0) as tmp
    where
        occupation = 'Professor'
    order by
        name asc
),
singers as (
    select
        name,
        @s_i := @s_i+1 as r
    from
        occupations,
        (select @s_i := 0) as tmp
    where
        occupation = 'Singer'
    order by
        name asc
),
actors as (
    select
        name,
        @a_i := @a_i+1 as r
    from
        occupations,
        (select @a_i := 0) as tmp
    where
        occupation = 'Actor'
    order by
        name asc
),
r_number as (
    select
        @i := @i+1 as r
    from
        occupations,
        (select @i := 0) as tmp
    where
        @i < (select max(cnt.num) from (select count(*) as num from occupations group by occupation) as cnt)
)

select
    doctors.name,
    professors.name,
    singers.name,
    actors.name
from
    r_number
left join
    doctors on r_number.r = doctors.r
left join
    professors on r_number.r = professors.r
left join
    singers on r_number.r = singers.r
left join
    actors on r_number.r = actors.r
;