MySql 계층적 조회 쿼리

MySQL 에는 안타깝게도 Oracle 의 start with, connect by 를 지원하는 함수가 없다.

때문에 아래와 같이 function 을 만들어서 사용한다.

예제 테이블) test.servers_group

create table test.servers_group 

(

 group_idx int auto_increment,     // P.K

 group_name varchar(40),           // group name

 group_depth int,                       // level

 group_order int,                        // group 순서

 parent_idx int,                          // parent group_idx

 manager_idx,                           // 관리자 idx (test.managers_info.idx)

 primary key(group_idx) 

)

----------------------------------------------------------------------------------

>> Create Function 

CREATE FUNCTION start_with_connect_by(value INT) RETURNS INT

NOT DETERMINISTIC

READS SQL DATA

BEGIN

        DECLARE _id INT;

        DECLARE _parent INT;

        DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;

        SET _parent = @id;

        SET _id = -1;

        IF @id IS NULL THEN

                RETURN NULL;

        END IF;

        LOOP

                SELECT  MIN(group_idx)

                INTO    @id

                FROM    test.servers_group

                WHERE   parent_idx = _parent

                        AND group_idx > _id;

                IF @id IS NOT NULL OR _parent = @start_with THEN

                        SET @level = @level + 1;

                        RETURN @id;

                END IF;

                SET @level := @level - 1;

                SELECT  group_idx, parent_idx

                INTO    _id, _parent

                FROM    test.servers_group

                WHERE   group_idx = _parent;

        END LOOP;

END

----------------------------------------------------------------------------------

>> 사용

SELECT  CONCAT(REPEAT('   ', group_depth - 1), CAST(sg.group_idx AS CHAR)) AS group_idx, group_name, parent_idx, group_depth, manager_idx

FROM    (

        SELECT  start_with_connect_by(group_idx) AS id, @level AS level

        FROM    (

                SELECT  @start_with := 0,

                        @id := @start_with,

                        @level := 0

                ) vars, test.servers_group

        WHERE   @id IS NOT NULL

        ) sg2

JOIN    test.servers_group sg

ON      sg.group_idx = sg2.id;

===================================================================================

Function 만들기 다른 예제 

CREATE TABLE test.dept_code ( 

code VARCHAR(50), 

name VARCHAR(50), 

parent_code VARCHAR(50) 

);

INSERT INTO test.dept_code VALUES( 'A001', '연구소' , '');

INSERT INTO test.dept_code VALUES( 'A002', '솔루션사업본부' , '');

INSERT INTO test.dept_code VALUES( 'A003', '기술지원부', '' );

INSERT INTO test.dept_code VALUES( 'A004', '개발1팀' , 'A001');

INSERT INTO test.dept_code VALUES( 'A005', '개발2팀' , 'A001');

INSERT INTO test.dept_code VALUES( 'A006', 'OJT' , 'A005');

CREATE FUNCTION GET_DEPT_TREE(value VARCHAR(50)) RETURNS VARCHAR(50) 

NOT DETERMINISTIC

READS SQL DATA

BEGIN

        DECLARE _code VARCHAR(50);

        DECLARE _parent VARCHAR(50);

        DECLARE CONTINUE HANDLER FOR NOT FOUND SET @code = NULL;

        SET _parent = @code;

        SET _code = '';

        

        IF @code IS NULL THEN

                RETURN NULL;

        END IF;

       LOOP

                SELECT  MIN(code)

                INTO    @code

                FROM    test.dept_code

                WHERE   parent_code = _parent

                        AND code > _code;

                IF @code IS NOT NULL OR _parent = @start_with THEN

                        SET @level = @level + 1;

                        RETURN @code;

                END IF;

                SET @level := @level - 1;

                SELECT  code, parent_code

                INTO    _code, _parent

                FROM    test.dept_code

                WHERE   code = _parent;

        END LOOP;

END

SELECT  dc.code, CONCAT(REPEAT('       ', dc2.lev-1), name) AS name, parent_code, CAST(dc2.lev AS CHAR) AS lev

FROM    (

        SELECT  GET_DEPT_TREE(code) AS code, @level as lev

        FROM    (

                SELECT  @start_with := '',

                        @code := @start_with,

                        @level := 0

                ) vars, test.dept_code

        WHERE   @code IS NOT NULL

        ) dc2

JOIN    test.dept_code dc

ON      dc.code = dc2.code;