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
|