계층형 메뉴 정렬 하는 쿼리
CREATE DATABASE test default CHARACTER SET UTF8;
use test;
DROP table IF EXISTS menu;
CREATE TABLE menu (
no int PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
parent int NULL,
depth int NOT NULL,
sequence int,
reg_date DATETIME,
modify_date DATETIME
)ENGINE=INNODB;
DESCRIBE menu;
DROP TABLE menu;
INSERT INTO menu(name, parent, depth , sequence, reg_date, modify_date) VALUES("메뉴1" ,0 ,1, 1, now(), now());
INSERT INTO menu(name, parent, depth , sequence, reg_date, modify_date) VALUES("메뉴1-1" ,1 ,2, 1, now(), now());
INSERT INTO menu(name, parent, depth , sequence, reg_date, modify_date) VALUES("메뉴1-2" ,1 ,2, 2, now(), now());
INSERT INTO menu(name, parent, depth , sequence, reg_date, modify_date) VALUES("메뉴1-1-1" ,2 ,3, 1, now(), now());
INSERT INTO menu(name, parent, depth , sequence, reg_date, modify_date) VALUES("메뉴2" ,0 ,1, 2, now(), now());
INSERT INTO menu(name, parent, depth , sequence, reg_date, modify_date) VALUES("메뉴2-1" ,5 ,2, 1, now(), now());
INSERT INTO menu(name, parent, depth , sequence, reg_date, modify_date) VALUES("메뉴2-2" ,5 ,2, 2, now(), now());
INSERT INTO menu(name, parent, depth , sequence, reg_date, modify_date) VALUES("메뉴2-1-1" ,6 ,3, 1, now(), now());
INSERT INTO menu(name, parent, depth , sequence, reg_date, modify_date) VALUES("메뉴1-1-2" ,2 ,3, 2, now(), now());
INSERT INTO menu(name, parent, depth , sequence, reg_date, modify_date) VALUES("메뉴2-1-2" ,6 ,3, 2, now(), now());
INSERT INTO menu(name, parent, depth , sequence, reg_date, modify_date) VALUES("메뉴2-1-3" ,6 ,3, 3, now(), now());
SELECT * FROM menu;
/* 계층형 mysql 쿼리 정렬 함수 */
DROP FUNCTION IF EXISTS hierarchy_connect_by_parent_eq_prior_no;
DELIMITER $$
CREATE FUNCTION hierarchy_connect_by_parent_eq_prior_no(value INT) RETURNS INTEGER
NOT DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE _parent INT;
DECLARE _rank INT;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;
SET _parent = @id;
SET _rank = 0;
IF @id IS NULL THEN
RETURN NULL;
END IF;
LOOP
SET @innerrank = 0;
SELECT p.no
INTO @id
FROM (
SELECT no, @innerrank := @innerrank+1 AS ran
FROM menu
WHERE COALESCE(parent, 0) = _parent
ORDER BY sequence
) p
WHERE p.ran > _rank LIMIT 0, 1;
IF @id IS NOT NULL OR _parent = @start_with THEN
SET @level = @level + 1;
RETURN @id;
END IF;
SET @level := @level - 1;
SET @innerrank = 0;
SELECT COALESCE(p.parent, 0), p.ran
INTO _parent, _rank
FROM (
SELECT no, parent, @innerrank := @innerrank+1 AS ran
FROM menu
WHERE COALESCE(parent, 0) = (
SELECT COALESCE(parent, 0) FROM menu WHERE no = _parent
)
ORDER BY sequence
) p
WHERE p.no = _parent;
END LOOP;
END;
$$
DELIMITER ;
/* 계층형 메뉴 정렬*/
SELECT CONCAT(REPEAT(' ', depth - 1), CAST(ou.no AS CHAR)) AS no, parent, depth, CONCAT(REPEAT(' ', depth - 1), CAST(ou.name AS CHAR)) AS name, sequence, level FROM (
SELECT mn.name , mn.no, depth , parent, sequence, level FROM (
SELECT hierarchy_connect_by_parent_eq_prior_no(no) AS no,
@level AS level
FROM (
SELECT @start_with := 0, @id := @start_with, @level := 0
) vars, menu
WHERE @id IS NOT NULL
) m
JOIN menu mn ON mn.no = m.no
) ou;
/* 계층형 mysql 쿼리 정렬 함수 - 특정 번호 */
DROP FUNCTION IF EXISTS hierarchy_connect_by_parent_eq_prior_id;
DELIMITER $$
CREATE FUNCTION hierarchy_connect_by_parent_eq_prior_id(value INT) RETURNS INTEGER
NOT DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE _no INT;
DECLARE _parent INT;
DECLARE _next INT;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET @no = NULL;
SET _parent = @no;
SET _no = -1;
IF @no IS NULL THEN
RETURN NULL;
END IF;
LOOP
SELECT MIN(no)
INTO @no
FROM menu
WHERE parent = _parent
AND no > _no;
IF @no IS NOT NULL OR _parent = @start_with THEN
SET @depth = @depth + 1;
RETURN @no;
END IF;
SET @depth := @depth - 1;
SELECT no, parent
INTO _no, _parent
FROM menu
WHERE no = _parent;
END LOOP;
END;
$$
DELIMITER ;
/* 조건에 따라 메뉴 출력 */
SELECT mn.name , mn.no, depth , parent, sequence
FROM (
SELECT no, @depth AS vars_depth FROM menu WHERE no= '원하는메뉴번호'
UNION ALL
SELECT hierarchy_connect_by_parent_eq_prior_id(no) AS no, @depth AS vars_depth
FROM (
SELECT @start_with := '원하는메뉴번호',
@no := @start_with,
@depth := 0
) vars, menu
WHERE @no IS NOT NULL
) fn
JOIN menu mn
ON mn.no = fn.no;
참고사이트
