mysql家譜表查詢某人所有後代

    我們經常會把一些樹形的結構放在一張表裡，例如家譜，菜單等。這種表最大的問題就是當我們需要查詢某個記錄的所有子記錄時，很難用簡單的sql搞定。我們可以專門寫個過程來做這種事情，下面是我寫的一個例子：

    樹形結構表存放的是家譜，表比較簡單，結構如下：   

家譜表

CREATE TABLE `people` (
    `id` INT(11) NOT NULL,
    `name` VARCHAR(50) NULL DEFAULT NULL,
    `pid` INT(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
);

    下面是查詢所有後代的過程：

查詢子孫過程

CREATE DEFINER=`root`@`%` PROCEDURE `getChildren`(IN `parentId` INT)
    LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT '擷取某人所有後代'
BEGIN
    #存放結果的暫存資料表
DROP TABLE IF EXISTS children;
CREATE TEMPORARY TABLE children SELECT 0 pLevel,p.* FROM `people` p WHERE id=parentId;
    #存放中間結果的暫存資料表
DROP TABLE IF EXISTS tem;
CREATE TEMPORARY TABLE tem SELECT id FROM `people` limit 0;

    #逐級填充後代
SET @pLevel=1;
    REPEAT
        #清空上次資料
TRUNCATE TABLE tem;
        #將當前level的後代id放入暫存資料表
INSERT INTO tem SELECT p.id FROM `people` P, children c 
WHERE p.pid=c.id AND    c.pLevel=(@pLevel-1);
        #將當前level的後代資料塞入結果暫存資料表
INSERT INTO children SELECT @pLevel pLevel,p.* FROM `people` p, tem t 
WHERE p.id=t.id;
SET @pLevel=@pLevel+1;
    UNTIL NOT EXISTS (SELECT * FROM tem) OR @pLevel > 10
END REPEAT;

    #調整表結構，刪除臨時列和不需要的資料
ALTER TABLE children DROP COLUMN pLevel;
DELETE FROM children WHERE id=parentId;

    #返回結果
SELECT * from children;
END

    說明：上面的方法只適合表比較小，效能要求不高的情況下，比如存放某個介面的菜單,或者人數比較少的部門的阻止關係。如果表比較大，效能開銷比較大，可能會非常慢，這個時候最好還是建個冗餘表，將所有的父子關係存一條記錄，這樣一個簡單的sql就能查詢出所有結果了。