[PostgreSQL] 계층형 쿼리 구현(스타크래프트 테란 건물)

1. 테이블 생성

CREATE TABLE building (
b_no serial NOT NULL,
b_cd varchar(30) NOT NULL,
b_en_nm varchar(100) NOT NULL,
b_kr_nm varchar(100) NULL,
mineral int4 NULL,
gas int4 NULL,
lv int4 NULL,
p_no int4 NULL,
species bpchar(1) NULL,
"TYPE" varchar(30) NULL,
del_yn bpchar(1) NULL DEFAULT 'N'::bpchar,
use_yn bpchar(1) NULL DEFAULT 'Y'::bpchar,
CONSTRAINT pk_building PRIMARY KEY (b_no),
CONSTRAINT uq_building UNIQUE (b_cd)
);

테이블 구조

2. 데이터 INSERT

INSERT INTO building (b_no,b_cd,b_en_nm,b_kr_nm,mineral,gas,lv,p_no,species,"TYPE",del_yn,use_yn) VALUES 

(5,'REFINERY','Refinery','가스채집장',NULL,NULL,1,NULL,'T',NULL,'N','Y')

,(2,'BARRACKS','Barracks','배럭',NULL,NULL,2,1,'T',NULL,'N','Y')

,(1,'COMMAND_CENTER','Command Center','커맨드 센터',NULL,NULL,1,NULL,'T',NULL,'N','Y')

,(13,'SUPPLY_DEPOT','Supply Depot','밥집',NULL,NULL,1,NULL,'T',NULL,'N','Y')

,(14,'ENGINEERING_BAY','Enginerring Bay','엔베',NULL,NULL,2,1,'T',NULL,'N','Y')

,(8,'ACADEMY','Academy','아카데미',NULL,NULL,2,2,'T',NULL,'N','Y')

,(6,'FACTORY','Factory','팩토리',NULL,NULL,2,2,'T',NULL,'N','Y')

,(7,'BUNKER','Bunker','벙커',NULL,NULL,2,2,'T',NULL,'N','Y')

,(12,'COMSAT_SATION','Comsat Station',NULL,NULL,NULL,3,8,'T',NULL,'N','Y')

,(15,'MISSILE_TURRET','Missile Turret',NULL,NULL,NULL,3,14,'T',NULL,'N','Y')

;

INSERT INTO building (b_no,b_cd,b_en_nm,b_kr_nm,mineral,gas,lv,p_no,species,"TYPE",del_yn,use_yn) VALUES 

(16,'SCIENCE_FACILITY','Science Facility',NULL,NULL,NULL,4,10,'T',NULL,'N','Y')

,(17,'CONTROL_TOWER','Control Tower',NULL,NULL,NULL,4,10,'T',NULL,'N','Y')

,(18,'PHYSICS_LAB','Physics Lab',NULL,NULL,NULL,5,16,'T',NULL,'N','Y')

,(19,'COVERT_OPS','Covert Ops',NULL,NULL,NULL,5,16,'T',NULL,'N','Y')

,(20,'NUCLEAR_SILO','Nuclear Silo',NULL,NULL,NULL,6,19,'T',NULL,'N','Y')

,(10,'STAR_PORT','Starport','',NULL,NULL,3,6,'T',NULL,'N','Y')

,(9,'ARMORY','Armory',NULL,NULL,NULL,3,6,'T',NULL,'N','Y')

,(11,'MACHINE_SHOP','Machine Shop',NULL,NULL,NULL,3,6,'T',NULL,'N','Y')

;

테이블 데이터

3. 쿼리 작성 및 수행

with recursive TECH_TREE as (

select B_NO,

       B_CD,

       P_NO,

       LV

from building

-- where B_NO = 1     -- 최상위 트리 지정

union 

select B.B_NO,

   B.B_CD,

   B.P_NO,

   B.LV

from building b 

inner join TECH_TREE T

on T.B_NO = B.P_NO

)

select T.B_NO as CHILD_NO,

       T.B_CD as CHILD_CD,

       T.LV   as CHILD_LV,

       T.P_NO as PARENT_NO,

       B.B_CD as PARENT_CD

from TECH_TREE T

LEFT join building B

on B.B_NO = T.P_NO

order by T.LV, T.P_NO;

쿼리 결과

4. TODO
 - LV 값에 따라 화면에 들여쓰기,, 접기, 펼치기 등 구현



※ 참고 사이트
https://www.postgresqltutorial.com/postgresql-recursive-query/
https://liquipedia.net/starcraft/Technology_tree
https://kugancity.tistory.com/entry/postgresql-%EC%9D%98-%EA%B8%B0%EB%B3%B8%ED%82%A4-%EC%9E%90%EB%8F%99-%EC%A6%9D%EA%B0%80