您可以将这个问题看作是该问题的后续问题：对闭包表层次数据结构中的子树进行排序
让我们考虑修改后的示例（新行名为 rating 在 category 表）：

--
-- Table `category`
--

CREATE TABLE IF NOT EXISTS `category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8_czech_ci NOT NULL,
  `rating` int(11) NOT NULL,
  `active` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `category` (`id`, `name`, `rating`, `active`) VALUES
(1, 'Cat 1', 0, 1),
(2, 'Cat 2', 0, 1),
(3, 'Cat  1.1', 0, 1),
(4, 'Cat  1.1.1', 2, 1),
(5, 'Cat 2.1', 0, 1),
(6, 'Cat 1.2', 2, 1),
(7, 'Cat 1.1.2', 3, 1);

--
-- Table `category_closure`
--

CREATE TABLE IF NOT EXISTS `category_closure` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `ancestor` int(11) DEFAULT NULL,
  `descendant` int(11) DEFAULT NULL,
  `depth` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_category_closure_ancestor_category_id` (`ancestor`),
  KEY `fk_category_closure_descendant_category_id` (`descendant`)
) ENGINE=InnoDB;

INSERT INTO `category_closure` (`id`, `ancestor`, `descendant`, `depth`) VALUES
(1, 1, 1, 0),
(2, 2, 2, 0),
(3, 3, 3, 0),
(4, 1, 3, 1),
(5, 4, 4, 0),
(7, 3, 4, 1),
(8, 1, 4, 2),
(10, 6, 6, 0),
(11, 1, 6, 1),
(12, 7, 7, 0),
(13, 3, 7, 1),
(14, 1, 7, 2),
(16, 5, 5, 0),
(17, 2, 5, 1);

多亏了bill karwin，我才能够根据数据的数字顺序对数据进行排序 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           | Rating: 0
|  3 | Cat 1.1    |      1 |       1 | 1,3         | Rating: 0
|  4 | Cat 1.1.1  |      1 |       3 | 1,3,4       | Rating: 2
|  7 | Cat 1.1.2  |      1 |       3 | 1,3,7       | Rating: 3
|  6 | Cat 1.2    |      1 |       1 | 1,6         | Rating: 2
+----+------------+--------+---------+-------------+

到目前为止还不错，现在我想用 rating 行起始位置 category table。应该是这样的：

+----+------------+--------+---------+-------------+
| id | name       | active | _parent | breadcrumbs |
+----+------------+--------+---------+-------------+
|  1 | Cat 1      |      1 |    NULL | 1           | Rating: 0
|  6 | Cat 1.2    |      1 |       1 | 1,6         |**Rating: 2**
|  3 | Cat 1.1    |      1 |       1 | 1,3         | Rating: 0
|  7 | Cat 1.1.2  |      1 |       3 | 1,3,7       |**Rating: 3**
|  4 | Cat 1.1.1  |      1 |       3 | 1,3,4       |**Rating: 2**
+----+------------+--------+---------+-------------+

所以所有的数据都应该两者兼有 breadcrumbs ASC 以及 rating DESC 不打破等级制度的秩序。一个查询就可以吗？这有可能吗？
谢谢。
更新：
以下是我根据比尔回答的第二部分所做的尝试：

SELECT c2.*, cc2.ancestor AS `_parent`,
  GROUP_CONCAT(c2.rating 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 |
+----+------------+--------+---------+-------------+
|  7 | Cat 1.1.2  |      1 |       3 | 3,3,3       |**Rating: 3**
|  6 | Cat 1.2    |      1 |       1 | 2,2         |**Rating: 2**
|  4 | Cat 1.1.1  |      1 |       3 | 2,2,2       |**Rating: 2**
|  1 | Cat 1      |      1 |    NULL | 0           | Rating: 0
|  3 | Cat 1.1    |      1 |       1 | 0,0         | Rating: 0
+----+------------+--------+---------+-------------+

也请注意 rating 值可以是 SIGNED （否定）也是。
可能的答案：
不使用两个根，请检查注解。

SELECT c2.*, cc2.ancestor AS `_parent`,
  GROUP_CONCAT(999-c3.rating 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 AS c3 ON (breadcrumb.ancestor = c3.id)
WHERE c1.id = 1/*__ROOT__*/ AND c1.active = 1
GROUP BY cc1.descendant
ORDER BY breadcrumbs;