SPATIUM Desktop
주소복사
About Operating System Languages Tools Favorites Notice Visit TEST  
     Android (3)
     Linux (1)
     MacOS (1)
     OS (1)
     Solaris10 (15)
     Windows (1)
     Windows Server (2)
     Windows XP (3)
   ID  
   Password  
  |  
  Location United States
  IP Address 3.238.24.209
2022. 06
1234
567891011
12131415161718
19202122232425
262728
29
30
Category  Languages, SQL
Writer 김태우 Date 2020-04-24 09:46:06 Visit 2015
DB hierarchy 테이블 쿼리(hierarchy)

 

DB hierarchy 테이블 쿼리(hierarchy)

가끔 카테리고리 테이블, Common 테이블 설계시 계층 구조로 설계하여 depth 를 자유롭게 구성하고 싶을때가 있다.

그러나, 몇몇 개발자들은 조금의 복잡도도 견디지 못하고, 계층별 테이블을 별도로 관리할 경우가 있다.

 

하기 테스트가 아이디가 될수 있을거 같다. 테스트 환경 : MariaDB

테이블 생성

CREATE TABLE `node_info` ( 
  `NODE_ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '노드아이디', 
  `PNODE_ID` int(11) NOT NULL COMMENT '부모노드아이디', 
  `NODE_PATH` varchar(200) DEFAULT NULL COMMENT '노드경로', 
  `NODE_NM` varchar(20) DEFAULT NULL COMMENT '노드명', 
  `DEPTH` int(11) DEFAULT '1' COMMENT '노드DEPTH', 
  `LEAF_YN` varchar(1) DEFAULT 'N' COMMENT '자식노드존재유무', 
  PRIMARY KEY (`NODE_ID`) 
) ;
 

샘플 데이터

INSERT INTO test2.node_info (PNODE_ID,NODE_PATH,NODE_NM,`DEPTH`,LEAF_YN) VALUES 
(0,'고객지원','고객지원',0,'Y')
,(1,'고객지원^장애처리','장애처리',1,'Y')
,(1,'고객지원^상품안내','상품안내',1,'Y')
,(3,'고객지원^상품안내^요금제','요금제',2,'N')
,(1,'고객지원^기타','기타',1,'N')
,(2,'고객지원^장애처리^VOD','VOD',2,'N')
;
 

쿼리

# 상위 노드 찾기 - 방법 1

 SELECT
@nid AS nid,
( SELECT @nid := pnode_id
FROM node_info
WHERE node_id = nid ) AS pnode_id
FROM
( SELECT @nid := 4 ) vars ,
node_info
WHERE
@nid <> 0 ;
 

# 하위 노드 찾기 - 방법 1

with recursive cte (node_id, node_path, pnode_id) as
(
 select     node_id,
            node_path,
            pnode_id
 from       node_info
 where      pnode_id = 1
 union all
 select     r.node_id,
            r.node_path,
            r.pnode_id
 from       node_info r
 inner join cte
         on r.pnode_id = cte.node_id
)
select * from cte;
 

#하위 노드 찾기 - 방법 2

SELECT
node_id
FROM
(
SELECT node_id, pnode_id
FROM node_info
ORDER BY pnode_id,node_id ) node_sorted,
( select @pv := 1) initial
WHERE
FIND_IN_SET(pnode_id, @pv)
AND LENGTH(@pv := concat(@pv, ',', node_id))
 
 
 
Tags  recursive, hierarchy, mariadb
  Relation Articles
[Languages-SQL] DB hierarchy 테이블 쿼리(hierarchy) (2020-04-24 09:46:06)
  Your Opinion
Member ID
150 letters
Copyright (C) SPATIUM. All rights reserved.
[SPATIUM]WebMaster Mail