排序闭包表分层数据结构



您可以将此问题视为该问题的后续问题:在闭包表分层数据结构中对子树进行排序

让我们考虑修改后的示例(在category表中有一个名为rating的新行(:

--
-- 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
+----+------------+--------+---------+-------------+

到目前为止还不错,现在我想使用category表中的rating行对结果进行排序。应该是这样的:

+----+------------+--------+---------+-------------+
| 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 ASCrating 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(负(。

可能的答案:

不使用2个根,请查看注释。

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;

EDIT-更新了排序参数我相信这就是你需要/想要的问题。由于category表中没有PARENT_ID列,我首先从闭包中获取所有根项,然后找到它们的所有子项,通过修改后的面包屑排序,其中最后一项不是当前叶的ID,而是其评级。因此,您可以通过评级进行反向排序,同时仍然保持层次结构级别。

SELECT category.id,name,rating,
(SELECT GROUP_CONCAT(CONCAT(LPAD(1000 - rating, 5, "0"), "#", ancestor) ORDER BY depth DESC) 
FROM category_closure LEFT JOIN category AS cat ON ancestor = cat.id WHERE descendant = category.id
) AS sorting
FROM category_closure
LEFT JOIN category ON descendant = category.id
WHERE ancestor IN
(SELECT ancestor FROM category_closure AS c1 
WHERE depth = 0 
AND NOT EXISTS(SELECT 1 FROM category_closure AS c2 
WHERE c2.descendant = c1.descendant AND depth > 0)
)
ORDER BY sorting

相关内容

  • 没有找到相关文章

最新更新