데이터베이스 질문과 답변
안녕하세요.
쿼리 작성하는 데 있어서 조언을 얻고 싶어 글을 올리게 되었습니다~
간단히 설명하면, 레벨이 1부터 4까지 있고
특정 레벨 1에 대하여 레벨 2,3,4가 달리게 됩니다.
테이블 구조는
CREATE TABLE `mt_department` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '[ID]',
`mt_company_id` int(10) unsigned NOT NULL COMMENT '[회사ID]',
`reference_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '[부모로우ID]',
`name` varchar(100) NOT NULL COMMENT '[부서명]',
`layer` enum('1','2','3','4') NOT NULL DEFAULT '1' COMMENT '[레이어]',
PRIMARY KEY (`id`),
KEY `fk_mt_department_mt_company_idx` (`mt_company_id`),
CONSTRAINT `fk_mt_department_mt_company` FOREIGN KEY (`mt_company_id`) REFERENCES `mt_company` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB
reference_id가 0인 경우 레벨 1(layer = 1)입니다.
만약 레벨 2인 로우가 등록되면 reference_id가 레벨 1(layer = 1)의 id를 가지게 됩니다.
등록되어 있는 데이터
+----+---------------+--------------+----------------+-------+---------------------+---------------------+------------+ | id | mt_company_id | reference_id | name | layer +----+---------------+--------------+----------------+-------+---------------------+---------------------+------------+ | 1 | 6 | 0 | Example name | 1 | 2 | 6 | 0 | Example name-2 | 1 | 3 | 6 | 1 | Layer-2 | 2 | 4 | 6 | 1 | Layer-22 | 2 | 5 | 6 | 4 | Layer-3 | 3 | 6 | 6 | 2 | Layer-222 | 2 +----+---------------+--------------+----------------+-------+---------------------+---------------------+------------+
제가 얻고 싶은 결과는 레벨1에 대한 하위 레벨들을 다 보여주고, 그 다음 레벨 1에 대한 하위레벨들을 다 보여주고 싶습니다.
+----+---------------+--------------+----------------+-------+---------------------+---------------------+------------+ | id | mt_company_id | reference_id | name | layer +----+---------------+--------------+----------------+-------+---------------------+---------------------+------------+ | 1 | 6 | 0 | Example name | 1 | 3 | 6 | 1 | Layer-2 | 2 | 4 | 6 | 1 | Layer-22 | 2 | 5 | 6 | 4 | Layer-3 | 3 | 2 | 6 | 0 | Example name-2 | 1 | 6 | 6 | 2 | Layer-222 | 2 +----+---------------+--------------+----------------+-------+---------------------+---------------------+------------+
Stored Procedure를 재귀적으로 생성하고, Wrapper Function을 생성하여 사용하는 방법 (참고 : https://code-examples.net/ko/q/13477c0) DROP PROCEDURE IF EXISTS getpath; DELIMITER $$ CREATE PROCEDURE getpath(IN in_id INT, OUT path TEXT) BEGIN DECLARE tempid INT; DECLARE temppid INT; DECLARE temppath TEXT; SET max_sp_recursion_depth = 10; SELECT id, reference_id FROM mt_department WHERE id = in_id INTO tempid, temppid; IF temppid = 0 THEN SET path = tempid; ELSE CALL getpath(temppid, temppath); SET path = CONCAT(temppath, '/', tempid); END IF; END$$ DELIMITER ; DROP FUNCTION IF EXISTS getpath; DELIMITER $$ CREATE FUNCTION getpath(in_id INT) RETURNS TEXT DETERMINISTIC BEGIN DECLARE res TEXT; CALL getpath(in_id, res); RETURN res; END$$ DELIMITER ; select id, mt_company_id, reference_id, name, layer, getpath(id) from mt_department order by getpath(id);
답변 감사합니다!
서비스에 도입할 수 있을지 알려주신 걸 토대로 상담해보겠습니다.
-- 샘플인긴한데 전 뷰를 만들어서 join해서 사용합니다.
ALTER VIEW V_CAR_MODEL_INFO AS
WITH RECURSIVE R_CAR_MODEL_INFO AS (
SELECT CAR_MODEL_CODE
, CAR_MODEL_NAME
, CAR_MODEL_UP_CODE
, 1 LV
, IFNULL(CAR_MODEL_NAME,'') CONNECT_BY_PATH
, CONVERT(IFNULL(CAR_MODEL_CODE,''),CHAR(100)) CONNECT_BY_CODE
, CONVERT(IFNULL(SORT_NO,'0'),CHAR(100)) CONNECT_BY_SORT
FROM CAR_MODEL_INFO
WHERE CAR_MODEL_UP_CODE = 0
UNION ALL
SELECT A.CAR_MODEL_CODE
, A.CAR_MODEL_NAME
, A.CAR_MODEL_UP_CODE
, LV + 1
, CONCAT ( B.CONNECT_BY_PATH , '/' , IFNULL(A.CAR_MODEL_NAME,'') )
, CONCAT ( B.CONNECT_BY_CODE , '/' , IFNULL(A.CAR_MODEL_CODE,'') )
, CONCAT ( B.CONNECT_BY_SORT , '/' , IFNULL(A.SORT_NO,'0') )
FROM CAR_MODEL_INFO A
, R_CAR_MODEL_INFO B
WHERE A.CAR_MODEL_UP_CODE = B.CAR_MODEL_CODE
)
SELECT CAR_MODEL_CODE
, CAR_MODEL_NAME
, CAR_MODEL_UP_CODE
, LV
, CONNECT_BY_PATH
, CONNECT_BY_CODE
, CONNECT_BY_SORT
FROM R_CAR_MODEL_INFO
) 버튼을 클릭하여 작성 하시면 됩니다.