자신이 속한 부서, 계층으로 나타내기

특정 계정의 속한 부서를 "IT부서>백앤드>백앤드1"팀 이렇게 표현하고자 합니다.

테이블 만들기

user 테이블

id	account	dept_id
1	dogineer	9

dept 테이블

id	name	p_id
1	index	1
2	IT본부	1
3	프론트	2
4	백앤드	2
5	프론트1팀	3
7	백앤드1팀	4
8	프론트2팀	3
9	백앤드2팀	4

간략하게 이야기하자면 'dogineer'가 속한 부서는 백앤드2팀입니다.

백앤드 2팀의 p_id는 4(백앤드)가 될 것이고,
백앤드는 p_id가 2인 값을 갖고 있으므로 IT본부(2)인 것을 확인할 수 있습니다

이렇게 재귀적으로 자신을 재참조하여 로직을 반복해서 계층을 만들려고 합니다.

부서 찾기

우선적으로 특정 계정의 부서를 찾아봅시다.

SELECT u.account, d.name AS deptName  
FROM user u, dept d 
WHERE u.account = "dogineer" AND dept_id = d.id

account	deptName
dogineer	백앤드2팀

계층으로 나타내기

SELECT u.account, CONCAT_WS(' > ',d3.name, d2.name, d.name) AS deptPath
FROM dept d
INNER JOIN user u ON d.id = e.dept_id
LEFT JOIN dept d2 ON d.p_id = d2.id
LEFT JOIN dept d3 ON d2.p_id = d3.id
WHERE u.account = "dogineer"

account	deptName
dogineer	IT본부>백앤드>백앤드2팀

CONCAT_WS 함수는 전달된 인수들을 구분자(' > ')로 이어붙여 하나의 문자열로 만듭니다.
여기서 d3.name, d2.name, d.name은 각각 최상위 부서, 상위 부서, 현재 부서의 이름을 나타냅니다.

dept 테이블과 user 테이블을 INNER JOIN하여 부서 정보와 해당 부서에 속한 직원 정보를 매칭합니다.
ON 절에서는 dept 테이블의 id 컬럼과 user 테이블의 dept_id 컬럼이 매칭되는 데이터를 가져와서
부서와 해당 부서에 속한 직원 정보를 연결해줍니다.

이런식으로 상위 부서의 dept id 컬럼을 조회합니다.

LEFT JOIN dept d2 ON d.p_id = d2.id
LEFT JOIN dept d3 ON d2.p_id = d3.id

마지막 WHERE 절을 이용해 찾고자 하는 계정을 적어줍니다.

WITH RECURSIVE 구문 사용

WITH RECURSIVE 임시 테이블을 생성하여 자신의 값을 참조해 값을 결정할 때 사용되는 구문

WITH RECURSIVE 구문을 사용하여 C라는 임시적인 테이블을 만들고,
그 안에 결과를 담아서 임시 C 테이블을 SELECT 하여 작성할 수도 있습니다.

WITH RECURSIVE C AS (
    SELECT u.account, CONCAT_WS(' > ',d3.name, d2.name, d.name) AS deptPath
    FROM dept d
    INNER JOIN user u ON d.id = e.dept_id
    LEFT JOIN dept d2 ON d.p_id = d2.id
    LEFT JOIN dept d3 ON d2.p_id = d3.id
) SELECT C.account, C.deptPath FROM C WHERE C.account = "dogineer"