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 |