DDL:
"cat_no" integer PRIMARY KEY,
"parent_cat_no" integer,
"title" character varying(200) NOT NULL
);
샘플 데이터 입력:
insert into "t_cat" ("cat_no", "parent_cat_no", "title") values (1, NULL, '정치');
insert into "t_cat" ("cat_no", "parent_cat_no", "title") values (2, 1, '국내정치');
insert into "t_cat" ("cat_no", "parent_cat_no", "title") values (3, 1, '해외정치');
insert into "t_cat" ("cat_no", "parent_cat_no", "title") values (4, NULL, '경제');
insert into "t_cat" ("cat_no", "parent_cat_no", "title") values (5, 4, '국내경제');
insert into "t_cat" ("cat_no", "parent_cat_no", "title") values (6, 4, '해외경제');
insert into "t_cat" ("cat_no", "parent_cat_no", "title") values (7, 1, '기타정치');
insert into "t_cat" ("cat_no", "parent_cat_no", "title") values (8, 5, '기타경제');
insert into "t_cat" ("cat_no", "parent_cat_no", "title") values (9, 2, '정당');
insert into "t_cat" ("cat_no", "parent_cat_no", "title") values (10, 9, '한나라당');
insert into "t_cat" ("cat_no", "parent_cat_no", "title") values (11, 9, '민주당');
SYS_CONNECT_BY_PATH를 구현한 SQL:
SELECT
CASE WHEN cat_no4 IS NOT NULL THEN cat_no4
WHEN cat_no3 IS NOT NULL THEN cat_no3
WHEN cat_no2 IS NOT NULL THEN cat_no2
WHEN cat_no1 IS NOT NULL THEN cat_no1
END AS cat_no,
CASE WHEN cat_no1 IS NOT NULL THEN cat_no1
WHEN cat_no2 IS NOT NULL THEN cat_no2
WHEN cat_no3 IS NOT NULL THEN cat_no3
WHEN cat_no4 IS NOT NULL THEN cat_no4
END AS root_sort,
TO_NUMBER(NVL2(title1, '1', '0')) +
TO_NUMBER(NVL2(title2, '1', '0')) +
TO_NUMBER(NVL2(title3, '1', '0')) +
TO_NUMBER(NVL2(title4, '1', '0')) AS cat_level,
NVL2(title1, '/'+title1, '') +
NVL2(title2, '/'+title2, '') +
NVL2(title3, '/'+title3, '') +
NVL2(title4, '/'+title4, '') AS cat_path
FROM
(
SELECT
t1.cat_no AS cat_no1,
t2.cat_no AS cat_no2,
t3.cat_no AS cat_no3,
t4.cat_no AS cat_no4,
t1.title AS title1,
t2.title AS title2,
t3.title AS title3,
t4.title AS title4
FROM
t_cat t1,
t_cat t2,
t_cat t3,
t_cat t4
WHERE
t2.parent_cat_no = t1.cat_no (+)
AND t3.parent_cat_no = t2.cat_no (+)
AND t4.parent_cat_no = t3.cat_no (+)
) t
ORDER
BY root_sort,
cat_path
