SPATIUM Desktop
주소복사
About Operating System Languages Tools Favorites Notice Visit TEST  
     ADK (3)
     android (3)
     ASP (5)
     ASP.Net (1)
     C# (5)
     HTML (9)
     JAVA (7)
     JavaScript (9)
     jQuery (5)
     Mobile Web (2)
     node.js (1)
     Oracle (8)
     PHP (7)
     Spring Framework (4)
     SQL (17)
     WPF (5)
   ID  
   Password  
  |  
  Location United States
  IP Address 3.238.4.24
2020. 10
123
45678910
11121314151617
18192021
22
2324
25262728293031
Category  Languages, SQL
Writer 김태우 Date 2020-04-24 09:46:06 Visit 415
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