MySQL 5.5 재귀쿼리 관련 질문(BOM 역전개)

Question

안녕하세요.BOM 역전개 관련 쿼리작성중 풀리지않는 부분이 있어 도움요청드립니다. MySql 이며 하위버전인 5.5버전을 사용해야 하기에with recursive 를 통한 재귀쿼리는 작성할 수 없는 상태입니다. 위와 같은 부모, 자식으로 구성된 테이블이 있으며 부모가 여러개가 있을수있는 BOM 구성입니다.정전개 부분은 문제없으나 역전개 처리시 부모가 1개 이상일때 원하는 결과를 뽑을수가 없어 도움 요청드립니다. 이미지상 우측 빨간색 강조된 부분의 결과를 구하고자 합니다.단계별로 부모의 갯수가 N개가 가능하게 처리되어야 합니다. 아래는 역전개를 위해 제가 작성한 쿼리이며 해당쿼리로는 한개의 역전개만 결과로 나오는데 ( 8 > 3 > 2 > 1 의 결과만 나옴 )원하는 결과인 이미지상의 (8 > 3, 5 > 2 >1 ) 결과가 나오게 처리하려면 어느부분을 손봐야될지with recursive 등을 사용하지않고(사용못함) 처리방안에 대해 도움 요청드립니다. ## 샘플 테이블 및 현재 작성된 쿼리입니다, DROP TABLE IF EXISTS &#96;bomtemp&#96;;CREATE TABLE IF NOT EXISTS &#96;bomtemp&#96; ( &#96;id&#96; int(11) NOT NULL, &#96;parentid&#96; int(11) NOT NULL, &#96;qty&#96; int(11) NOT NULL, PRIMARY KEY (&#96;id&#96;,&#96;parentid&#96;)) ENGINE&#61;InnoDB DEFAULT CHARSET&#61;utf8; INSERT INTO bomtemp VALUES(1, 0, 1);INSERT INTO bomtemp VALUES(2, 1, 10);INSERT INTO bomtemp VALUES(3, 2, 10);INSERT INTO bomtemp VALUES(4, 3, 10);INSERT INTO bomtemp VALUES(5, 0, 1);INSERT INTO bomtemp VALUES(6, 5, 10);INSERT INTO bomtemp VALUES(8, 3, 10);INSERT INTO bomtemp VALUES(8, 5, 10); SELECT &#64;R AS _id, ( SELECT &#64;R :&#61; parentid FROM bomtemp WHERE id &#61; _id ) AS parentid, &#64;L :&#61; &#64;L &#43; 1 AS lvlFROM ( SELECT &#64;R :&#61; 8, &#64;L :&#61; 0) VARS, bomtemp HWHERE &#64;R <> 0

르매 · Answer

adjacency 모델로 테이블을 구성하셨군요.이런 방식을 한 번 고려해 보셔도 좋을 듯 합니다.https://purumae.tistory.com/192?category&#61;652830

르매 · Answer

사전 준비 1DROP DATABASE IF EXISTS latin1_test; CREATE DATABASE latin1_test CHARACTER SET latin1; USE latin1_test; -- ----------------------------------------------------- -- FUNCTION usf_base252_ntos -- ----------------------------------------------------- SET NAMES 'latin1'; SET COLLATION_CONNECTION = 'latin1_general_ci'; SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; DELIMITER $$ DROP FUNCTION IF EXISTS usf_base252_ntos$$ CREATE DEFINER=CURRENT_USER() FUNCTION usf_base252_ntos( pi_int_number int UNSIGNED -- 0 ~ 4,032,758,015 (252^4 - 1) ) RETURNS char(4) DETERMINISTIC SQL SECURITY DEFINER CONTAINS SQL COMMENT ' author : doeyull.kim e-mail : purumae@gmail.com created date : 2017-12-06 description : encode base252 (number to string) parameter : pi_int_number int UNSIGNED ' BEGIN DECLARE v_iny_quotient tinyint UNSIGNED; DECLARE v_i tinyint UNSIGNED DEFAULT 1; SET @tmp = ''; WHILE v_i <= 4 DO SET v_iny_quotient = FLOOR(pi_int_number / POWER(252, (4 - v_i))); SET pi_int_number = pi_int_number % POWER(252, (4 - v_i)); SET v_iny_quotient = CASE -- skip CHAR(0), CHAR(37), CHAR(95), CHAR(127) WHEN v_iny_quotient <= 35 THEN v_iny_quotient + 1 WHEN v_iny_quotient BETWEEN 36 AND 93 THEN v_iny_quotient + 2 ELSE v_iny_quotient + 3 END; SET @tmp = CONCAT(@tmp, CHAR(v_iny_quotient)); SET v_i = v_i + 1; END WHILE; RETURN @tmp; END$$ DELIMITER ; -- ----------------------------------------------------- -- FUNCTION usf_base252_ston -- ----------------------------------------------------- SET NAMES 'latin1'; SET COLLATION_CONNECTION = 'latin1_general_ci'; SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; DELIMITER $$ DROP FUNCTION IF EXISTS usf_base252_ston$$ CREATE DEFINER=CURRENT_USER() FUNCTION usf_base252_ston( pi_chr_string char(4) ) RETURNS int UNSIGNED DETERMINISTIC SQL SECURITY DEFINER CONTAINS SQL COMMENT ' author : doeyull.kim e-mail : purumae@gmail.com created date : 2017-12-06 description : decode base252 (string to number) parameter : pi_chr_string char(4) ' BEGIN DECLARE v_a tinyint UNSIGNED; DECLARE v_b tinyint UNSIGNED; DECLARE v_c tinyint UNSIGNED; DECLARE v_d tinyint UNSIGNED; DECLARE v_int_number int UNSIGNED; SET v_a = ASCII(SUBSTRING(pi_chr_string, 4, 1)); SET v_b = ASCII(SUBSTRING(pi_chr_string, 3, 1)); SET v_c = ASCII(SUBSTRING(pi_chr_string, 2, 1)); SET v_d = ASCII(SUBSTRING(pi_chr_string, 1, 1)); SET v_int_number = CASE WHEN v_d >= 96 THEN v_d - 3 WHEN v_d BETWEEN 38 AND 95 THEN v_d - 2 ELSE v_d - 1 END * 16003008 + CASE WHEN v_c >= 96 THEN v_c - 3 WHEN v_c BETWEEN 38 AND 95 THEN v_c - 2 ELSE v_c - 1 END * 63504 + CASE WHEN v_b >= 96 THEN v_b - 3 WHEN v_b BETWEEN 38 AND 95 THEN v_b - 2 ELSE v_b - 1 END * 252 + CASE WHEN v_a >= 96 THEN v_a - 3 WHEN v_a BETWEEN 38 AND 95 THEN v_a - 2 ELSE v_a - 1 END; RETURN v_int_number; END$$ DELIMITER ; 사전 준비 2DROP DATABASE IF EXISTS utf8_test; CREATE DATABASE utf8_test CHARACTER SET utf8; USE utf8_test; DROP TABLE IF EXISTS `tally`; CREATE TABLE `tally` ( `n` SMALLINT UNSIGNED NOT NULL COMMENT '1 ~ 10,000 정수', PRIMARY KEY (`n`)) ENGINE = InnoDB COMMENT = 'mutex 용도의 테이블'; INSERT `tally` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),(41),(42),(43),(44),(45),(46),(47),(48),(49),(50),(51),(52),(53),(54),(55),(56),(57),(58),(59),(60),(61),(62),(63),(64),(65),(66),(67),(68),(69),(70),(71),(72),(73),(74),(75),(76),(77),(78),(79),(80),(81),(82),(83),(84),(85),(86),(87),(88),(89),(90),(91),(92),(93),(94),(95),(96),(97),(98),(99),(100); 솔루션USE utf8_test; DROP TABLE IF EXISTS `bomtemp`; CREATE TABLE `bomtemp` ( `id` int(10) UNSIGNED NOT NULL, `parentid` int(10) UNSIGNED NULL, `qty` int(10) UNSIGNED NOT NULL, `the_path` varchar(149) NOT NULL COLLATE latin1_general_ci, `depth` tinyint(3) UNSIGNED NOT NULL, PRIMARY KEY (`id`), UNIQUE INDEX `uix-bomtemp-the_path` (the_path) ) ENGINE=InnoDB; -- 부모 등록 (1) INSERT INTO bomtemp VALUES(1, NULL, 1, latin1_test.usf_base252_ntos(1), 0); -- 1의 자식 등록 (2) INSERT bomtemp SELECT 2, `id`, 10, CONCAT_WS(CHAR(127), the_path, latin1_test.usf_base252_ntos(2)), depth + 1 FROM bomtemp FORCE INDEX FOR JOIN (PRIMARY) WHERE `id` = 1; -- 2의 자식 등록 (3) INSERT bomtemp SELECT 3, `id`, 10, CONCAT_WS(CHAR(127), the_path, latin1_test.usf_base252_ntos(3)), depth + 1 FROM bomtemp FORCE INDEX FOR JOIN (PRIMARY) WHERE `id` = 2; -- 3의 자식 등록 (4) INSERT bomtemp SELECT 4, `id`, 10, CONCAT_WS(CHAR(127), the_path, latin1_test.usf_base252_ntos(4)), depth + 1 FROM bomtemp FORCE INDEX FOR JOIN (PRIMARY) WHERE `id` = 3; -- 3의 자식 등록 (8) INSERT bomtemp SELECT 8, `id`, 10, CONCAT_WS(CHAR(127), the_path, latin1_test.usf_base252_ntos(8)), depth + 1 FROM bomtemp FORCE INDEX FOR JOIN (PRIMARY) WHERE `id` = 3; -- 부모 등록 (5) INSERT INTO bomtemp VALUES(5, NULL, 1, latin1_test.usf_base252_ntos(5), 0); -- 전체 정전개 SELECT * FROM bomtemp FORCE INDEX FOR ORDER BY (`uix-bomtemp-the_path`) ORDER BY the_path; -- 1번 정전개 SELECT * FROM bomtemp FORCE INDEX FOR ORDER BY (`uix-bomtemp-the_path`) WHERE the_path LIKE CONCAT(latin1_test.usf_base252_ntos(1), '%' COLLATE latin1_general_ci) ORDER BY the_path; -- 8번 역전개 SELECT STRAIGHT_JOIN P.id, P.parentid, P.qty, P.depth FROM ( SELECT latin1_test.usf_base252_ston(SUBSTRING_INDEX(SUBSTRING_INDEX(E.the_path, CHAR(127), - n), CHAR(127), 1)) AS id FROM bomtemp E FORCE INDEX FOR JOIN (PRIMARY) INNER JOIN tally T FORCE INDEX FOR JOIN (PRIMARY) ON T.n <= depth + 1 WHERE E.id = 8 ) S INNER JOIN bomtemp P FORCE INDEX FOR JOIN (PRIMARY) ON P.id = S.id;

nathak · Answer

결과만 나오게 작성 해봤습니다. 성능 및 유효성 체크는 안해봤구요 ㅎㅎ;; SELECT DISTINCT * FROM ( SELECT &#64;R AS _id, ( SELECT &#64;R :&#61; parentid FROM bomtemp WHERE id &#61; _id AND (parentid <> &#64;TEMP) ) AS parentid, IF(&#64;R &#61; 0, &#64;L :&#61;0 , &#64;L :&#61; &#64;L &#43; 1) AS lvl, IF(&#64;L &#61; 1, &#64;TEMP :&#61; &#64;R, &#64;TEMP) AS temp, IF(&#64;R &#61; 0, &#64;R :&#61; 8, null) AS init FROM ( SELECT &#64;R :&#61; 8, &#64;L :&#61; 0, &#64;TEMP :&#61; 0 ) VARS, bomtemp H ) M

MySQL 5.5 재귀쿼리 관련 질문(BOM 역전개)

3개의 답변

Q&A 목록