根据子级查询父级

 

具体数据库根据情况修改

树形结构的数据很多地方都用得到，(产品)父子分类、(角色)父子权限、(应用、模块)父子组

 

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;