MySQL排序层次结构数据 [英] Mysql sorting Hierarchical data

问题描述

我有一个问题，上个星期已经解决了，还不能解决..我可以用join查询子菜单，但是我不能订购它.

I have a question and working last week with this and couldnt solve yet.. I can query submenus with join but i cant order it.

我有一张这样的桌子

| id   |  name           |  parent | order  |    
|-------------------------------------------|
| 1    | menu1           |  0      |   1    |
| 2    | submenu1        |  1      |   2    |
| 3    | submenu2        |  1      |   1    |
| 4    | subsubmenu      |  2      |   1    |
| 5    | subsubsubmenu:) |  4      |   1    |
| 6    | menu2           |  0      |   3    |
| 7    | menu3           |  0      |   2    |
|-------------------------------------------|

我想得到这样的东西.

| - menu1
      | - submenu2
      | - submenu1
              | - subsubmenu
                       | - subsubsubmenu:)
| - menu3
| - menu2

有人可以给我一个想法如何处理吗?谢谢

Can anybody give me an idea how can handle this? Thank you

推荐答案

我研究了 http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/，并找到了解决您问题的方法.我想您已经有了解决方案，但是对于其他正在寻找相同解决方案的人，我在这里回答.

I have studied http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/ and found a solution for your problem. I guess you already have your solution but for any other who is searching for same solution I am answering here.

我的解决方案也适用于关系表，因为我们不能在关系表的父字段中设置零(0).它将为NULL，而且我的解决方案也适用于关系表.

My solution will work for relational Tables also as we can't set zero (0) in parent field in relational table. It will be NULL and my solution works perfectly for relational tables also.

DROP FUNCTION IF EXISTS hierarchy_connect_by_parent_eq_prior_id;
DELIMITER $$
CREATE FUNCTION hierarchy_connect_by_parent_eq_prior_id(value INT) RETURNS INTEGER
NOT DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE _parent INT;
    DECLARE _rank INT;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;

    SET _parent = @id;
    SET _rank = 0;

    IF @id IS NULL THEN
            RETURN NULL;
    END IF;

    LOOP
        SET @innerrank = 0;
        SELECT p.id 
        INTO   @id
        FROM   (
                SELECT   id, @innerrank := @innerrank+1 AS rank 
                FROM     yourTable 
                WHERE    COALESCE(parent, 0) = _parent 
                ORDER BY yourField
                ) p 
        WHERE   p.rank > _rank LIMIT 0, 1;
        IF @id IS NOT NULL OR _parent = @start_with THEN
                SET @level = @level + 1;
                RETURN @id;
        END IF;
        SET @level := @level - 1;
        SET @innerrank = 0;
        SELECT COALESCE(p.parent, 0), p.rank
        INTO   _parent, _rank
        FROM   (
                SELECT id, parent, @innerrank := @innerrank+1 AS rank
                FROM    yourTable
                WHERE   COALESCE(parent, 0) = (
                    SELECT COALESCE(parent, 0) FROM yourTable WHERE id = _parent
                    ) 
                ORDER BY yourField
               ) p
        WHERE p.id = _parent;
    END LOOP;       
END;
$$
DELIMITER ;

请使用表名称替换yourTable，并使用要对数据进行排序的字段名称替换yourField.

Please replace yourTable with your table name and yourField with your field name by which you want to sort your data.

SELECT ou.* FROM (
    SELECT hi.id, parent, yourField FROM (
        SELECT hierarchy_connect_by_parent_eq_prior_id(id) AS id, 
            @level AS level 
            FROM (
                SELECT @start_with := 0, @id := @start_with, @level := 0
                 ) vars, yourTable 
            WHERE @id IS NOT NULL
        ) ho 
    JOIN yourTable hi ON hi.id = ho.id
) ou

请用您要显示的表名替换yourTable，并用您要显示的字段名替换yourField.

Please replace yourTable with your table name and yourField with your field name which you want to display.

这将产生所需的结果.我测试了一下，效果很好.

This will produce the result as you required. I tested it and works well.

这里是 http://sqlfiddle.com/#!9/9d060d/2观看它的运行情况.

Here is http://sqlfiddle.com/#!9/9d060d/2 to see it in action.

谢谢.

这篇关于MySQL排序层次结构数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！