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
