按父节点和子节点排序的MySQL树

Question

我将尝试使用这个参考链接（http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/）在MYSQL中展示树形结构，注意此方法只支持同一表格内的1个父节点。
如果你想要和另一个表格进行连接，则无法实现。以下是一个例子:

菜单表格:

+----+--------+-------+
| id |  name  | order |
| 1  | Father |   0   |
| 2  | Father |   1   |
| 3  | Son    |   0   |
| 4  | Child  |   1   |
| 5  | Granson|   2   |
+----+--------+-------+

表关系

+----+---------+-----------+
| id | menu_id | parent_id |
| 1  |    1    |    NULL   |
| 2  |    2    |    NULL   |
| 3  |    3    |     1     |
| 4  |    4    |     3     |
| 5  |    5    |     4     |
+----+---------+-----------+

执行SELECT语句

SELECT child_menu.*, menu.* FROM menu, relations AS child_menu
WHERE menu.id = child_menu.menu_id
GROUP BY menu_id

我有这个：

+----+--------+-------+
| id |  name  | order |
| 1  | Father |   0   |
| 2  | Father |   1   |
| 3  | Son    |   0   |
| 4  | Child  |   1   |
| 5  | Granson|   2   |
+----+--------+-------+

我正在尝试在同一SELECT语句中按顺序排列他们的子项。
从我在示例中看到的情况来看，只有当父项在同一张表中时才有效。
有人能帮帮我吗？ 谢谢

+----+--------+-------+
| id |  name  | order |
| 1  | Father |   0   |
| 3  | Son    |   0   |
| 4  | Child  |   1   |
| 5  | Granson|   2   |
| 2  | Father |   1   |
+----+--------+-------+

I.E. Father
       Son
         Child
            Grandson

- Daniel Arantes Loverde

---

你的理想输出具体应该是什么样子？ - dmcnelis

按照父、子、孙的顺序和顺序排序 - Daniel Arantes Loverde

子查询对你来说是一个选项吗？请指定您想要如何查看输出。 - Hank

只需要一个能够工作的程序，无论如何实现，但要保持简单和可扩展性。 - Daniel Arantes Loverde

@JackLoverde — 如果您附上期望的输出，您的问题将更加清晰。 - skalee

Answer 1

除了Mike Hillyer博客中展示的方法之外，还有其他组织分层数据的方式。我喜欢使用一种称为传递闭包表或缩写为闭包表的方法。在这种设计中，您存储层次结构中的每个路径，作为祖先/后代对。

create table closure (
    ancestor int,
    descendant int,
    length int,
    primary key (ancestor,descendant),
    key (descendant,ancestor)
);
insert into closure values
(1,1,0),
(1,3,1),
(1,4,2),
(1,5,3),
(2,2,0),
(3,3,0),
(3,4,1),
(3,5,2),
(4,4,0),
(4,5,1),
(5,5,0);

请注意，此集合甚至包括长度为零的“路径”，即菜单项是其自身的“父级”。 现在，您可以将每个菜单项m连接到其祖先a的每个集合中，方法是连接到m是后代的路径。从那里，回到祖先集合中的菜单项o，并可以访问顺序。 使用GROUP_CONCAT()将每个祖先链中的顺序制作成“面包屑”字符串，这将成为一个字符串，您可以按其排序以获得所需的菜单顺序。

SELECT m.*, GROUP_CONCAT(o.`order` ORDER BY a.length DESC) AS breadcrumbs
FROM menu AS m
INNER JOIN closure AS a ON a.descendant = m.id
INNER JOIN menu AS o ON a.ancestor = o.id
GROUP BY m.id
ORDER BY breadcrumbs;

+----+----------+-------+-------------+
| id | name     | order | breadcrumbs |
+----+----------+-------+-------------+
|  1 | Father1  |     0 | 0           |
|  3 | Son      |     0 | 0,0         |
|  4 | Child    |     1 | 0,0,1       |
|  5 | Grandson |     2 | 0,0,1,2     |
|  2 | Father2  |     1 | 1           |
+----+----------+-------+-------------+

请注意，面包屑是按字符串排序的，所以如果您的某些order数字有2或3位数，您将得到不规则的结果。确保您所有的order数字都有相同的位数。

---

作为另一种选择，您可以简单地将面包屑字符串存储在原始菜单表中：

ALTER TABLE menu ADD COLUMN breadcrumbs VARCHAR(255);
UPDATE menu SET breadcrumbs = '0,0,1,2' WHERE id = 5;
etc.

那么你可以执行一个更简单的查询：

SELECT * FROM menu ORDER BY breadcrumbs;

如果您更改菜单项的顺序，那么您需要手动重新计算所有受影响的面包屑字符串。

- Bill Karwin

---

最理想的情况是创建一个新表并添加一个新列。但事实并非如此，查询必须使用已经存在的表。 - Daniel Arantes Loverde

Answer 2

如果您能创建一个MySQL自定义函数，那么您可以在执行时动态地创建您的家谱树，使用类似以下内容的方式：

DELIMITER //

CREATE FUNCTION fnFamilyTree ( id INT ) RETURNS TEXT
BEGIN
   SET @tree = '';
   SET @qid = id;
   WHILE (@qid > 0) DO
      SELECT IFNULL(r.parent_id,-1),m.ordr INTO @pid,@ordr FROM Relations r JOIN Menu m ON m.id = r.id WHERE r.id = @qid LIMIT 1;
      SET @tree = CONCAT(@ordr,' ',@tree);
      SET @qid = @pid;
   END WHILE;
   RETURN RTRIM(@tree);
END//

DELIMITER ;

接下来的SQL语句可以给你提供所需的序列：

SELECT m.id
      ,m.name
      ,r.parent_id
      ,fnFamilyTree( r.id )
  FROM Relations r
  JOIN Menu m
    ON m.id = r.menu_id
 ORDER BY fnFamilyTree( r.id )
;

在http://sqlfiddle.com/#!2/199c25/1上尝试一下。结果如下：

ID  NAME            PARENT_ID        FNFAMILYTREE( R.ID )
1   Father          (null)           0
3   Son             1                0 0
4   Child           3                0 0 1
5   Granson         4                0 0 1 2
2   Father          (null)           1

至少我认为这就是你想要的。

针对实际模式的更新

用户定义函数：

DELIMITER //

CREATE FUNCTION fnFamilyTree ( id INT ) RETURNS TEXT
BEGIN
   SET @tree = '';
   SET @qid = id;
   WHILE (@qid > 0) DO
      SELECT IFNULL(r.`menu_master_id`,-1),m.`order` INTO @pid,@order FROM `menu_has_menu_master` r JOIN `menu` m ON m.`id` = r.`menu_id` WHERE r.`menu_id` = @qid LIMIT 1;
      SET @tree = CONCAT(LPAD(@order,5,'0'),' ',@tree);
      SET @qid = @pid;
   END WHILE;
   RETURN RTRIM(@tree);
END
//

DELIMITER ;

查询：

SELECT m.id
      ,m.name
      ,r.menu_master_id
      ,fnFamilyTree( r.menu_id )
  FROM menu_has_menu_master r
  JOIN menu m
    ON m.id = r.menu_id
 ORDER BY fnFamilyTree( r.menu_id )
;

结果位于 http://sqlfiddle.com/#!2/cb0384

ID      NAME                  MENU_MASTER_ID    FNFAMILYTREE( R.MENU_ID )
8       Dashboard             (null)            00001
6       Seções do Site      (null)            00002
7       Home                  6                 00002 00003
14      Catalogos             6                 00002 00004
15      Marcas                14                00002 00004 00004
16      Categoria 1           14                00002 00004 00006
9       Arquivos              (null)            00007
1       Administração       (null)            00127
3       Usuarios              1                 00127 00001
5       Secões do iPocket    1                 00127 00001
13      Default Setups        1                 00127 00002
4       Logs                  1                 00127 00003

- gwc

---

当我在MySQL中应用时，出现了一些问题，它会持续加载而永远不会显示结果。这里是我的真实表的CREATES和INSERTS：http://www.loverde.com.br/ipocket_tables.txt 如果您不介意进行修订... 谢谢。 - Daniel Arantes Loverde

完美，没有改变我的表格，非常优雅的解决方案！恭喜！应得的！ - Daniel Arantes Loverde

Answer 3

你需要进行自关联来显示关系。在子菜单上添加left join关系作为child_menu，你需要添加left join关系作为r2，其中r2.parent_id = child_menu.id ...

- Micromega

---

你说的"fiddle"是什么意思？请友善一点，我使用谷歌翻译。 - Daniel Arantes Loverde

通过访问这个网站，我只得到了错误提示，无法访问。我认为他们那边出了问题。如果您要给我打负分，请尝试先解决问题然后再告诉我您是如何解决的，否则请不要打负分。 - Daniel Arantes Loverde