mysql 存树 闭包表_在闭包表分层数据结构中对子树进行排序

小编典典

这个问题不仅针对闭包表而且针对其他存储分层数据的方法也经常出现。在任何设计中都不容易。

我为Closure

Table提出的解决方案涉及一个额外的联接。树中的每个节点都连接到其祖先链，就像“面包屑”类型查询一样。然后，使用GROUP_CONCAT()将面包屑折叠为逗号分隔的字符串，并按树中的深度对ID号进行排序。现在您有了一个可以用来排序的字符串。

SELECT c2.*, cc2.ancestor AS `_parent`,

GROUP_CONCAT(breadcrumb.ancestor ORDER BY breadcrumb.depth DESC) AS breadcrumbs

FROM category AS c1

JOIN category_closure AS cc1 ON (cc1.ancestor = c1.id)

JOIN category AS c2 ON (cc1.descendant = c2.id)

LEFT OUTER JOIN category_closure AS cc2 ON (cc2.descendant = c2.id AND cc2.depth = 1)

JOIN category_closure AS breadcrumb ON (cc1.descendant = breadcrumb.descendant)

WHERE c1.id = 1/*__ROOT__*/ AND c1.active = 1

GROUP BY cc1.descendant

ORDER BY breadcrumbs;

+----+------------+--------+---------+-------------+

| id | name | active | _parent | breadcrumbs |

+----+------------+--------+---------+-------------+

| 1 | Cat 1 | 1 | NULL | 1 |

| 3 | Cat 1.1 | 1 | 1 | 1,3 |

| 4 | Cat 1.1.1 | 1 | 3 | 1,3,4 |

| 7 | Cat 1.1.2 | 1 | 3 | 1,3,7 |

| 6 | Cat 1.2 | 1 | 1 | 1,6 |

+----+------------+--------+---------+-------------+

注意事项：

id值应具有统一的长度，因为排序“ 1,3”，“ 1,6”和“ 1,327”可能不会给出您想要的顺序。但是将排序“ 001,003”和“ 001,006”以及“ 001,327”。因此，您需要以1000000+开始的id值，或者ZEROFILL在category_closure表中将其用于祖先和后代。

在此解决方案中，显示顺序取决于类别ID的数字顺序。id值的数字顺序可能不代表您要显示树的顺序。或者，您可能希望自由地更改显示顺序，而与数字id值无关。或者，您可能希望同一类别数据出现在一棵以上的树中，每棵树的显示顺序不同。

如果需要更大的自由度，则需要将排序顺序值与ID分开存储，解决方案将变得更加复杂。但是在大多数项目中，可以使用快捷方式，将类别ID的双重职责指定为树的显示顺序。

发表您的评论：

是的，您可以将“同级排序顺序”存储为闭合表中的另一列，然后使用该值而不是ancestor构建面包屑字符串。但是，如果这样做，最终将导致大量数据冗余。就是说，给定的祖先存储在多行中，每条路径都从其降序存储。因此，您必须为所有这些行上的同级排序顺序存储相同的值，这会导致出现异常的风险。

另一种方法是创建另一个表，只有 一个 在树中每个不同的祖先行，并加入到该表以获得同级次序。

CREATE TABLE category_closure_order (

ancestor INT PRIMARY KEY,

sibling_order SMALLINT UNSIGNED NOT NULL DEFAULT 1

);

SELECT c2.*, cc2.ancestor AS `_parent`,

GROUP_CONCAT(o.sibling_order ORDER BY breadcrumb.depth DESC) AS breadcrumbs

FROM category AS c1

JOIN category_closure AS cc1 ON (cc1.ancestor = c1.id)

JOIN category AS c2 ON (cc1.descendant = c2.id)

LEFT OUTER JOIN category_closure AS cc2 ON (cc2.descendant = c2.id AND cc2.depth = 1)

JOIN category_closure AS breadcrumb ON (cc1.descendant = breadcrumb.descendant)

JOIN category_closure_order AS o ON breadcrumb.ancestor = o.ancestor

WHERE c1.id = 1/*__ROOT__*/ AND c1.active = 1

GROUP BY cc1.descendant

ORDER BY breadcrumbs;

+----+------------+--------+---------+-------------+

| id | name | active | _parent | breadcrumbs |

+----+------------+--------+---------+-------------+

| 1 | Cat 1 | 1 | NULL | 1 |

| 3 | Cat 1.1 | 1 | 1 | 1,1 |

| 4 | Cat 1.1.1 | 1 | 3 | 1,1,1 |

| 7 | Cat 1.1.2 | 1 | 3 | 1,1,2 |

| 6 | Cat 1.2 | 1 | 1 | 1,2 |

+----+------------+--------+---------+-------------+

2020-05-17