[Elice sql] cte..는 계속 헷갈림.

 

수정 전

- ... 아 이거 알았다고 생각했으나.. 착각이었음..

-- MEMBER 테이블로 계층형질의를 실시하고 lvl, member_name, manager_name을 조회하세요.

with recursive cte as (
    select member_id, member_name, manager_id, 0 as lvl
    from MEMBER
    WHERE manager_id is null
    union all 
    select a.member_id, a.member_name, a.manager_id, b.lvl+1 as lvl
    from cte b
    left outer join MEMBER a 
    on b.manager_id = a.member_id

)

select lvl, member_name, manager_id
from cte
order by member_id;

 

수정 후

- 아 정신차리자 진짜...

- 문제 제대로 파악 안 하냐..

- 그리고 저거 계층할 때 on 관계를 잘 생각해서 해야 함. 

-  맨 처음 lvl 0 일 때의 결과를 잘 생각할 것 

 

-- MEMBER 테이블로 계층형질의를 실시하고 lvl, member_name, manager_name을 조회하세요.

with recursive cte as (
    select member_id, member_name, manager_id, 0 as lvl
    from MEMBER
    WHERE manager_id is null
    union all 
    select a.member_id, a.member_name, a.manager_id, b.lvl+1 as lvl
    from cte b
    join MEMBER a 
    on a.manager_id = b.member_id -- on 기준 제대로 파악할 것 

)

select c.lvl, c.member_name, m.member_name as manager_name -- 문제 똑바로 읽을 것 
from cte c
left outer join MEMBER m on c.manager_id = m.member_id -- 여기 on도
order by c.member_id;