mysql递归(树)父子类

问题描述

我是 mysql 新手.这是我的桌子:

I am new in mysql. This is my table:

类别表:

id | name        | prent
----------------------------
1  |  os         | null
2  | linux       | 1
3  | ubuntu      | 2
4  | xubuntu     | 3
5  | lubuntu     | 3
6  | zubuntu     | 3
7  | zubuntu 2   | 6
8  | suse        | 2
9  | fedora      | 2
10 | windowse    | 1
11 | windowse xp | 10
12 | windowse 7  | 10
13 | windowse 8  | 10
14 | food        | null
15 | dance       | null

每个类别都有一个父类别，我想准备它们以显示在下拉菜单中.

Each category has a parent and I want to prepare them to show in a drop-down menu.

这就是我想要的:

id | name          | depth
----------------------------
1  | os            | 0
2  | -linux        | 1
3  | --ubuntu      | 2
4  | ---xubuntu    | 3
5  | ---lubuntu    | 3
6  | ---zubuntu    | 3
7  | ----zubuntu 2 | 4
8  | --suse        | 2
9  | --fedora      | 2
10 | -windows      | 1
11 | --windows  xp | 2
12 | --windows  7  | 2
13 | --windows  8  | 2
14 | food          | 0
15 | dance         | 0

在这里，类别没有顺序，我的代码必须为远离父母的子类别提供顺序.根据每个类别的父级的深度提供名称前的缩进.每个类别的子项数量没有限制，但类别总数不会超过100个.

Here, categories are not in order and my code has to provide order for children categories far from their parents. Indentation before name is provided based on depth of parents of each category. There is no limit for number of children of each category however the total number of categories will not exceed 100.

是否有任何查询会给出这样的结果?我更喜欢可以在 PHP 框架中以 活动记录 形式运行的查询.>

Is there any query that gives such result? I prefer a query that can be run in form of active record in a PHP framework.

推荐答案

这个 Thread 引导我.感谢@RolandoMySQLDBA

This Thread led me. Thanks to @RolandoMySQLDBA

DELIMITER $$
DROP FUNCTION IF EXISTS `GetAncestry` $$
CREATE FUNCTION `GetAncestry` (GivenID INT) RETURNS VARCHAR(1024)
DETERMINISTIC
BEGIN
    DECLARE rv VARCHAR(1024);
    DECLARE cm CHAR(1);
    DECLARE ch INT;

    SET rv = '';
    SET cm = '';
    SET ch = GivenID;
    WHILE ch > 0 DO
        SELECT IFNULL(`prent`,-1) INTO ch FROM
        (SELECT `prent` FROM Table1 WHERE id = ch) A;
        IF ch > 0 THEN
            SET rv = CONCAT(rv,cm,ch);
            SET cm = ',';
        END IF;
    END WHILE;
    RETURN rv;

END $$
DELIMITER ;

一个有效的fiddle这里.

SELECT id,GetAncestry(id) as parents from Table1 where id = 7;

ID  PARENTS
7   6,3,2,1