parent, child, display_seq(전시순번)있는 트리구조를 화면에 뿌려주는 로직입니다.

기본적인 로직은 레벨, 전시순번을 조합하여 SORT_STR 이라는 컬럼을 생성후 정렬해주는

방식입니다.

보시고 개선점이 있으면 글을 남겨주세요..

SELECT
AAAAAA.DEPT_ID AS DEPT_ID,
AAAAAA.DEPT_LEVEL AS DEPT_LEVEL,
DDDDDDD.dept_code AS dept_code,
DDDDDDD.DEPT_NAME AS DEPT_NAME,
DDDDDDD.DEPT_TYPE AS DEPT_TYPE,
DDDDDDD.DISPLAY_YN AS DISPLAY_YN,
DDDDDDD.DISPLAY_SEQ AS DISPLAY_SEQ,
DDDDDDD.TEMPLATE_ID AS TEMPLATE_ID,
DDDDDDD.DESCRIPTION AS DESCRIPTION,
DDDDDDD.CREATE_PERSON AS CREATE_PERSON,
DDDDDDD.CREATE_DATE AS CREATE_DATE,
DDDDDDD.UPDATE_PERSON AS UPDATE_PERSON,
DDDDDDD.UPDATE_DATE AS UPDATE_DATE,
DDDDDDD.SVC_ID AS SVC_ID,
DDDDDDD.STATUS AS STATUS,
DDDDDDD.LIMIT_CD AS LIMIT_CD
FROM (
SELECT C.DEPT_ID, C.PARENT_ID, 0 DEPT_LEVEL, D.DISPLAY_SEQ, CASE WHEN ( LEN (D.DISPLAY_SEQ) =2) THEN '0'+convert(varchar(20),D.DISPLAY_SEQ)+'000000000000000' ELSE convert(varchar(20),D.DISPLAY_SEQ)+'000000000000000' END AS SORT_STR FROM CAR_DEPT_PARENT c, CAR_DEPT d WHERE c.DEPT_ID = d.DEPT_ID AND PARENT_ID = 'ROOT'
UNION ALL
SELECT A.DEPT_ID, A.PARENT_ID, 1 DEPT_LEVEL , DD.DISPLAY_SEQ, CASE WHEN ( LEN (DD.DISPLAY_SEQ) =2) THEN B.SORT_STR+'0'+convert(varchar(20),DD.DISPLAY_SEQ)+'000000000000' ELSE B.SORT_STR+convert(varchar(20),DD.DISPLAY_SEQ)+'000000000000' END AS SORT_STR
FROM CAR_DEPT_PARENT A,
(SELECT C.DEPT_ID, PARENT_ID, 0 DEPT_LEVEL, CASE WHEN ( LEN (D.DISPLAY_SEQ) =2) THEN '0'+convert(varchar(20),D.DISPLAY_SEQ) ELSE convert(varchar(20),D.DISPLAY_SEQ) END AS SORT_STR FROM CAR_DEPT_PARENT c, CAR_DEPT D WHERE PARENT_ID = 'ROOT' and c.dept_id = d.dept_id) B,
CAR_DEPT DD
WHERE A.PARENT_ID = B.DEPT_ID
AND A.DEPT_ID = DD.DEPT_ID
UNION ALL
SELECT AA.DEPT_ID, AA.PARENT_ID, 2 DEPT_LEVEL , DDD.DISPLAY_SEQ, CASE WHEN ( LEN (DDD.DISPLAY_SEQ) =2) THEN BB.SORT_STR+'0'+convert(varchar(20),DDD.DISPLAY_SEQ)+'000000000' ELSE BB.SORT_STR+convert(varchar(20),DDD.DISPLAY_SEQ)+'000000000' END AS SORT_STR
FROM CAR_DEPT_PARENT AA,
(SELECT A.DEPT_ID, A.PARENT_ID, 1 DEPT_LEVEL , CASE WHEN ( LEN (DD.DISPLAY_SEQ) =2) THEN B.SORT_STR+'0'+convert(varchar(20),DD.DISPLAY_SEQ) ELSE B.SORT_STR+convert(varchar(20),DD.DISPLAY_SEQ) END AS SORT_STR
FROM CAR_DEPT_PARENT A,
(SELECT C.DEPT_ID, PARENT_ID, 0 DEPT_LEVEL, CASE WHEN ( LEN (D.DISPLAY_SEQ) =2) THEN '0'+convert(varchar(20),D.DISPLAY_SEQ) ELSE convert(varchar(20),D.DISPLAY_SEQ) END AS SORT_STR FROM CAR_DEPT_PARENT c, CAR_DEPT d WHERE PARENT_ID = 'ROOT' and c.dept_id = d.dept_id) B,
CAR_DEPT DD
WHERE A.PARENT_ID = B.DEPT_ID AND A.DEPT_ID=DD.DEPT_ID) BB,
CAR_DEPT DDD
WHERE AA.PARENT_ID = BB.DEPT_ID
AND DDD.DEPT_ID = AA.DEPT_ID
UNION ALL
SELECT AAA.DEPT_ID, AAA.PARENT_ID, 3 DEPT_LEVEL , DDDD.DISPLAY_SEQ, CASE WHEN ( LEN (DDDD.DISPLAY_SEQ) =2) THEN BBB.SORT_STR+'0'+convert(varchar(20),DDDD.DISPLAY_SEQ)+'000000' ELSE BBB.SORT_STR+convert(varchar(20),DDDD.DISPLAY_SEQ)+'000000' END AS SORT_STR
FROM CAR_DEPT_PARENT AAA,
(SELECT AA.DEPT_ID, AA.PARENT_ID, 2 DEPT_LEVEL, CASE WHEN ( LEN (DDD.DISPLAY_SEQ) =2) THEN BB.SORT_STR+'0'+convert(varchar(20),DDD.DISPLAY_SEQ) ELSE BB.SORT_STR+convert(varchar(20),DDD.DISPLAY_SEQ) END AS SORT_STR
FROM CAR_DEPT_PARENT AA,
(SELECT A.DEPT_ID, A.PARENT_ID, 1 DEPT_LEVEL, CASE WHEN ( LEN (DD.DISPLAY_SEQ) =2) THEN B.SORT_STR+'0'+convert(varchar(20),DD.DISPLAY_SEQ) ELSE B.SORT_STR+convert(varchar(20),DD.DISPLAY_SEQ) END AS SORT_STR
FROM CAR_DEPT_PARENT A,
(SELECT C.DEPT_ID, PARENT_ID, 0 DEPT_LEVEL, CASE WHEN ( LEN (D.DISPLAY_SEQ) =2) THEN '0'+convert(varchar(20),D.DISPLAY_SEQ) ELSE convert(varchar(20),D.DISPLAY_SEQ) END AS SORT_STR FROM CAR_DEPT_PARENT c, CAR_DEPT d WHERE PARENT_ID = 'ROOT' and c.dept_id = d.dept_id) B,
CAR_DEPT DD
WHERE A.PARENT_ID = B.DEPT_ID AND A.DEPT_ID=DD.DEPT_ID) BB,
CAR_DEPT DDD
WHERE AA.PARENT_ID = BB.DEPT_ID AND AA.DEPT_ID=DDD.DEPT_ID) BBB,
CAR_DEPT DDDD
WHERE AAA.PARENT_ID = BBB.DEPT_ID
AND DDDD.DEPT_ID = AAA.DEPT_ID
UNION ALL
SELECT AAAA.DEPT_ID, AAAA.PARENT_ID, 4 DEPT_LEVEL , DDDDD.DISPLAY_SEQ, CASE WHEN ( LEN (DDDDD.DISPLAY_SEQ) =2) THEN BBBB.SORT_STR+'0'+convert(varchar(20),DDDDD.DISPLAY_SEQ)+'000' ELSE BBBB.SORT_STR+convert(varchar(20),DDDDD.DISPLAY_SEQ)+'000' END AS SORT_STR
FROM CAR_DEPT_PARENT AAAA,
(SELECT AAA.DEPT_ID, AAA.PARENT_ID, 3 DEPT_LEVEL , DDDD.DISPLAY_SEQ, CASE WHEN ( LEN (DDDD.DISPLAY_SEQ) =2) THEN BBB.SORT_STR+'0'+convert(varchar(20),DDDD.DISPLAY_SEQ) ELSE BBB.SORT_STR+convert(varchar(20),DDDD.DISPLAY_SEQ) END AS SORT_STR
FROM CAR_DEPT_PARENT AAA,
(SELECT AA.DEPT_ID, AA.PARENT_ID, 2 DEPT_LEVEL, CASE WHEN ( LEN (DDD.DISPLAY_SEQ) =2) THEN BB.SORT_STR+'0'+convert(varchar(20),DDD.DISPLAY_SEQ) ELSE BB.SORT_STR+convert(varchar(20),DDD.DISPLAY_SEQ) END AS SORT_STR
FROM CAR_DEPT_PARENT AA,
(SELECT A.DEPT_ID, A.PARENT_ID, 1 DEPT_LEVEL, CASE WHEN ( LEN (DD.DISPLAY_SEQ) =2) THEN B.SORT_STR+'0'+convert(varchar(20),DD.DISPLAY_SEQ) ELSE B.SORT_STR+convert(varchar(20),DD.DISPLAY_SEQ) END AS SORT_STR
FROM CAR_DEPT_PARENT A,
(SELECT C.DEPT_ID, PARENT_ID, 0 DEPT_LEVEL, CASE WHEN ( LEN (D.DISPLAY_SEQ) =2) THEN '0'+convert(varchar(20),D.DISPLAY_SEQ) ELSE convert(varchar(20),D.DISPLAY_SEQ) END AS SORT_STR FROM CAR_DEPT_PARENT c, CAR_DEPT d WHERE PARENT_ID = 'ROOT' and c.dept_id = d.dept_id) B,
CAR_DEPT DD
WHERE A.PARENT_ID = B.DEPT_ID AND A.DEPT_ID=DD.DEPT_ID) BB,
CAR_DEPT DDD
WHERE AA.PARENT_ID = BB.DEPT_ID AND AA.DEPT_ID=DDD.DEPT_ID) BBB,
CAR_DEPT DDDD
WHERE AAA.PARENT_ID = BBB.DEPT_ID
AND DDDD.DEPT_ID = AAA.DEPT_ID) BBBB,
CAR_DEPT DDDDD
WHERE AAAA.PARENT_ID = BBBB.DEPT_ID
AND AAAA.DEPT_ID = DDDDD.DEPT_ID
UNION ALL
SELECT AAAAA.DEPT_ID, AAAAA.PARENT_ID, 5 DEPT_LEVEL , DDDDDD.DISPLAY_SEQ, CASE WHEN ( LEN (DDDDDD.DISPLAY_SEQ) =2) THEN BBBBB.SORT_STR+'0'+convert(varchar(20),DDDDDD.DISPLAY_SEQ) ELSE BBBBB.SORT_STR+convert(varchar(20),DDDDDD.DISPLAY_SEQ) END AS SORT_STR
FROM CAR_DEPT_PARENT AAAAA,
(SELECT AAAA.DEPT_ID, AAAA.PARENT_ID, 4 DEPT_LEVEL , DDDDD.DISPLAY_SEQ, CASE WHEN ( LEN (DDDDD.DISPLAY_SEQ) =2) THEN BBBB.SORT_STR+'0'+convert(varchar(20),DDDDD.DISPLAY_SEQ) ELSE BBBB.SORT_STR+convert(varchar(20),DDDDD.DISPLAY_SEQ) END AS SORT_STR
FROM CAR_DEPT_PARENT AAAA,
(SELECT AAA.DEPT_ID, AAA.PARENT_ID, 3 DEPT_LEVEL , DDDD.DISPLAY_SEQ, CASE WHEN ( LEN (DDDD.DISPLAY_SEQ) =2) THEN BBB.SORT_STR+'0'+convert(varchar(20),DDDD.DISPLAY_SEQ) ELSE BBB.SORT_STR+convert(varchar(20),DDDD.DISPLAY_SEQ) END AS SORT_STR
FROM CAR_DEPT_PARENT AAA,
(SELECT AA.DEPT_ID, AA.PARENT_ID, 2 DEPT_LEVEL, CASE WHEN ( LEN (DDD.DISPLAY_SEQ) =2) THEN BB.SORT_STR+'0'+convert(varchar(20),DDD.DISPLAY_SEQ) ELSE BB.SORT_STR+convert(varchar(20),DDD.DISPLAY_SEQ) END AS SORT_STR
FROM CAR_DEPT_PARENT AA,
(SELECT A.DEPT_ID, A.PARENT_ID, 1 DEPT_LEVEL, CASE WHEN ( LEN (DD.DISPLAY_SEQ) =2) THEN B.SORT_STR+'0'+convert(varchar(20),DD.DISPLAY_SEQ) ELSE B.SORT_STR+convert(varchar(20),DD.DISPLAY_SEQ) END AS SORT_STR
FROM CAR_DEPT_PARENT A,
(SELECT C.DEPT_ID, PARENT_ID, 0 DEPT_LEVEL, CASE WHEN ( LEN (D.DISPLAY_SEQ) =2) THEN '0'+convert(varchar(20),D.DISPLAY_SEQ) ELSE convert(varchar(20),D.DISPLAY_SEQ) END AS SORT_STR FROM CAR_DEPT_PARENT c, CAR_DEPT d WHERE PARENT_ID = 'ROOT' and c.dept_id = d.dept_id) B,
CAR_DEPT DD
WHERE A.PARENT_ID = B.DEPT_ID AND A.DEPT_ID=DD.DEPT_ID) BB,
CAR_DEPT DDD
WHERE AA.PARENT_ID = BB.DEPT_ID AND AA.DEPT_ID=DDD.DEPT_ID) BBB,
CAR_DEPT DDDD
WHERE AAA.PARENT_ID = BBB.DEPT_ID
AND DDDD.DEPT_ID = AAA.DEPT_ID) BBBB,
CAR_DEPT DDDDD
WHERE AAAA.PARENT_ID = BBBB.DEPT_ID
AND AAAA.DEPT_ID = DDDDD.DEPT_ID
) BBBBB,
CAR_DEPT DDDDDD
WHERE AAAAA.PARENT_ID = BBBBB.DEPT_ID
AND AAAAA.DEPT_ID = DDDDDD.DEPT_ID
) AAAAAA, CAR_DEPT DDDDDDD
WHERE
AAAAAA.DEPT_ID = DDDDDDD.DEPT_ID
ORDER BY AAAAAA.SORT_STR