mysql递归（树）父子类别 [英] mysql recursive(tree) parent child category

问题描述

我是新的在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框架中以活动记录的形式运行的查询。 p>

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.

推荐答案

此 感谢@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 ;

有效 小提琴 此处。

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

ID  PARENTS
7   6,3,2,1

这篇关于mysql递归（树）父子类别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！