具体数据库根据情况修改
树形结构的数据很多地方都用得到,(产品)父子分类、(角色)父子权限、(应用、模块)父子组
MySQL
CREATE TABLE filler (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT
) ENGINE=Memory;
CREATE TABLE hierarchy (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
parent int(10) unsigned NOT NULL,
PRIMARY KEY (id),
KEY ix_hierarchy_parent (parent, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DELIMITER $$
CREATE PROCEDURE prc_filler(cnt INT)
BEGIN
DECLARE _cnt INT;
SET _cnt = 1;
WHILE _cnt <= cnt DO
INSERT
INTO filler
SELECT _cnt;
SET _cnt = _cnt + 1;
END WHILE;
END
$$;
DELIMITER $$
CREATE FUNCTION hierarchy_connect_by_parent_eq_prior_id(value INT) RETURNS INT
NOT DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE _id INT;
DECLARE _parent INT;
DECLARE _next 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(id)
INTO @id
FROM hierarchy
WHERE parent = _parent
AND id > _id;
IF @id IS NOT NULL OR _parent = @start_with THEN
SET @level = @level + 1;
RETURN @id;
END IF;
SET @level := @level - 1;
SELECT id, parent
INTO _id, _parent
FROM hierarchy
WHERE id = _parent;
END LOOP;
END
$$;
START TRANSACTION;
CALL prc_filler(18);
COMMIT;
INSERT
INTO hierarchy (id, parent)
SELECT id, id DIV 3
FROM filler;
SELECT CONCAT(REPEAT(' ', level - 1), CAST(_id AS CHAR)) AS treeitem,
parent,
`level`
FROM (
SELECT @r AS _id,
(
SELECT @r := parent
FROM hierarchy
WHERE id = _id
) AS parent,
@l := @l + 1 AS `level`
FROM (
SELECT @r := 10,
@l := 0
) vars,
hierarchy h
WHERE @r <> 0
) q;
原文(英文)地址:http://explainextended.com/2009/07/22/hierarchial-queries-in-mysql-identifying-trees/
PostgreSQL
片段一
-- Function: pdm.get_product_category_parent_path(numeric, boolean)
-- DROP FUNCTION pdm.get_product_category_parent_path(numeric, boolean);
CREATE OR REPLACE FUNCTION pdm.get_product_category_parent_path(IN category_id numeric, IN _self boolean, OUT parent_ids numeric[])
RETURNS numeric[] AS
$BODY$
DECLARE
_parent_id numeric default category_id;
_sql record;
_p integer default 0;
BEGIN
select parent_category_id,name from pdm.product_category where product_category_id=_parent_id into _sql;
if found then
--是否添加自己
if _self then
parent_ids := array_prepend(_parent_id,parent_ids);
end if;
_parent_id := _sql.parent_category_id;
_p := 0;
loop
_p := _p+1;
select product_category_id,parent_category_id from pdm.product_category where product_category_id=_parent_id into _sql;
exit when not found or _p >= 20;--循环大于20次就强制退出,防止死循环。;
_parent_id := _sql.parent_category_id;
parent_ids := array_prepend(_sql.product_category_id,parent_ids);
end loop;
end if;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
ALTER FUNCTION pdm.get_product_category_parent_path(numeric, boolean)
OWNER TO postgre;
片段二
-- Function: pdm.get_product_category_parent_path(numeric)
-- DROP FUNCTION pdm.get_product_category_parent_path(numeric);
CREATE OR REPLACE FUNCTION pdm.get_product_category_parent_path(IN category_id numeric, OUT parent_ids numeric[], OUT names character varying[])
RETURNS record AS
$BODY$
DECLARE
_parent_id numeric default category_id;
_sql record;
_p integer default 0;
BEGIN
select parent_category_id from pdm.product_category where product_category_id=_parent_id into _sql;
if found then
_parent_id := _sql.parent_category_id;
_p := 0;
loop
_p := _p+1;
select product_category_id,parent_category_id ,name from pdm.product_category where product_category_id=_parent_id into _sql;
exit when not found or _p >= 20;--循环大于20次就强制退出,防止死循环。;可指定 IN loopnum bigint
_parent_id := _sql.parent_category_id;
parent_ids := array_prepend(_sql.product_category_id,parent_ids);
names := array_prepend(_sql.name,names);
end loop;
end if;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
ALTER FUNCTION pdm.get_product_category_parent_path(numeric)
OWNER TO postgre;