这个问题不仅针对闭包表而且针对其他存储分层数据的方法也经常出现。在任何设计中都不容易。
我为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 |
+----+------------+--------+---------+-------------+
注意事项：
发表您的评论：
是的，您可以将“同级排序顺序”存储为闭合表中的另一列，然后使用该值而不是ancestor构建面包屑字符串。但是，如果这样做，最终将导致大量数据冗余。就是说，给定的祖先存储在多行中，每条路径都从其降序存储。因此，您必须为所有这些行上的同级排序顺序存储相同的值，这会导致出现异常的风险。
另一种方法是创建另一个表，只有 @H_301_26@一个 在树中每个不同的祖先行，并加入到该表以获得同级次序。
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 |
+----+------------+--------+---------+-------------+
602392714
清零编程群