5y8Ftasn

1DROP TABLE IF EXISTS `Category` ;
2 
3CREATE TABLE IF NOT EXISTS `Category` (
4  `idx` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '고유값',
5  `parentIdx` INT UNSIGNED NOT NULL COMMENT '부모고유값',
6  `title` VARCHAR(100) NOT NULL COMMENT '제목',
7  PRIMARY KEY (`idx`))
8ENGINE = InnoDB
9DEFAULT CHARACTER SET = utf8
10COMMENT = '카테고리 테이블';
11
12insert into Category set parentIdx=0, title='root';
13insert into Category set parentIdx=1, title='음악';
14insert into Category set parentIdx=2, title='발라드';
15insert into Category set parentIdx=2, title='댄스';
16insert into Category set parentIdx=2, title='랩/힙합';
17insert into Category set parentIdx=2, title='R&B/소울';
18insert into Category set parentIdx=2, title='인디음악';
19insert into Category set parentIdx=2, title='록/메탈';
20insert into Category set parentIdx=2, title='트로트';
21insert into Category set parentIdx=2, title='포크/블루스';
22insert into Category set parentIdx=1, title='미디어';
23
24
25DELIMITER //
26CREATE FUNCTION hierarchy_connect_by_parentIdx_eq_prior_idx(value INT) RETURNS INT
27NOT DETERMINISTIC
28READS SQL DATA
29BEGIN
30        DECLARE _idx INT;
31        DECLARE _parentIdx INT;
32        DECLARE _next INT;
33        DECLARE CONTINUE HANDLER FOR NOT FOUND SET @idx = NULL;
34 
35        SET _parentIdx = @idx;
36        SET _idx = -1;
37 
38        IF @idx IS NULL THEN
39                RETURN NULL;
40        END IF;
41 
42        LOOP
43                SELECT  MIN(idx)
44                INTO    @idx
45                FROM    Category
46                WHERE   parentIdx = _parentIdx
47                        AND idx > _idx;
48                IF @idx IS NOT NULL OR _parentIdx = @start_with THEN
49                        SET @level = @level + 1;
50                        RETURN @idx;
51                END IF;
52                SET @level := @level - 1;
53                SELECT  idx, parentIdx
54                INTO    _idx, _parentIdx
55                FROM    Category
56                WHERE   idx = _parentIdx;
57        END LOOP;      
58END
59//
60DELIMITER ;
61
62SELECT  CONCAT(REPEAT('    ', level - 1), hi.title) AS treeitem, parentIdx, level
63FROM    (
64        SELECT  hierarchy_connect_by_parentIdx_eq_prior_idx(idx) AS idx, @level AS level
65        FROM    (
66                SELECT  @start_with := 0,
67                        @idx := @start_with,
68                        @level := 0
69                ) vars, Category
70        WHERE   @idx IS NOT NULL
71        ) ho
72JOIN    Category hi
73ON      hi.idx = ho.idx