mysql Closure Table_MySQL Closure Table分层数据库-如何以正确的顺序提取信息

我有一个使用Closure

Table方法保存分层数据的MySQL数据库。这个问题后面有一个简单的示例数据库创建脚本。目前，我的问题是如何以正确的顺序将数据从数据库中拉出？我当前正在使用以下select语句。

SELECT `TreeData`.`iD`, `TreeData`.`subsectionOf`,

CONCAT(REPEAT('-', `TreePaths`.`len`),`TreeData`.`name`),

`TreePaths`.`len`,`TreePaths`.`ancestor`,`TreePaths`.`descendant`

FROM `TreeData`

LEFT JOIN `TreePaths` ON `TreeData`.`iD` = `TreePaths`.`descendant`

WHERE `TreePaths`.`ancestor` = 1

ORDER BY `TreeData`.`subsectionOrder`

它会提取正确的信息，但顺序不正确。

示例数据库创建带有示例数据的脚本。

-- Simple Sample

SET FOREIGN_KEY_CHECKS=0;

DROP TRIGGER IF EXISTS Tree_Insert;

DROP TRIGGER IF EXISTS Tree_Update;

DROP TABLE IF EXISTS TreePaths;

DROP TABLE IF EXISTS TreeData;

SET FOREIGN_KEY_CHECKS=1;

CREATE TABLE `TreeData` (

`iD` INT NOT NULL, -- PK

`subsectionOf` INT, -- Parent ID & FK

`subsectionOrder` INT, -- Oder of Subsections

`name` NVARCHAR(500) NOT NULL, -- Name for the entry

PRIMARY KEY (`iD`),

FOREIGN KEY (`subsectionOf`) REFERENCES TreeData(`iD`) ON DELETE CASCADE,

INDEX(`name`)

) ENGINE = MYISAM;

-- Trigger to update the EntryPaths table for new entries

DELIMITER //

CREATE TRIGGER `Tree_Insert` AFTER INSERT ON `TreeData` FOR EACH ROW

BEGIN

INSERT INTO `TreePaths` (`ancestor`, `descendant`, `len`)

SELECT `ancestor`, NEW.`iD`, len + 1 FROM `TreePaths`

WHERE `descendant` = NEW.`subsectionOf`

UNION ALL SELECT NEW.`iD`, NEW.`iD`, 0;

END; //

DELIMITER ;

DELIMITER //

CREATE TRIGGER `Tree_Update` BEFORE UPDATE ON `TreeData` FOR EACH ROW

BEGIN

-- From http://www.mysqlperformanceblog.com/2011/02/14/moving-subtrees-in-closure-table/

IF OLD.`subsectionOf` != NEW.`subsectionOf` THEN

-- Remove the node from its current parent

DELETE a FROM `TreePaths` AS a

JOIN `TreePaths` AS d ON a.`descendant` = d.`descendant`

LEFT JOIN `TreePaths` AS x

ON x.`ancestor` = d.`ancestor` AND x.`descendant` = a.`ancestor`

WHERE d.`ancestor` = OLD.`iD` AND x.`ancestor` IS NULL;

-- Add the node to its new parent

INSERT `TreePaths` (`ancestor`, `descendant`, `len`)

SELECT supertree.`ancestor`, subtree.`descendant`, supertree.`len`+subtree.`len`+1

FROM `TreePaths` AS supertree JOIN `TreePaths` AS subtree

WHERE subtree.`ancestor` = OLD.`iD`

AND supertree.`descendant` = NEW.`subsectionOf`;

END IF;

END; //

DELIMITER ;

CREATE TABLE `TreePaths` (

`ancestor` INT NOT NULL,

`descendant` INT NOT NULL,

`len` INT NOT NULL,

PRIMARY KEY (`ancestor`, `descendant`),

FOREIGN KEY (`ancestor`) REFERENCES TreeData(`iD`) ON DELETE CASCADE,

FOREIGN KEY (`descendant`) REFERENCES TreeData(`iD`) ON DELETE CASCADE

) ENGINE = MYISAM;

INSERT INTO `TreeData` VALUES(1, NULL, NULL, 'Root A');

INSERT INTO `TreeData` VALUES(2, 1, 1, 'Item 1');

INSERT INTO `TreeData` VALUES(3, 1, 2, 'Item 2');

INSERT INTO `TreeData` VALUES(4, 1, 3, 'Item 3');

INSERT INTO `TreeData` VALUES(5, 2, 2, 'Item 1 Sub Item 2');

INSERT INTO `TreeData` VALUES(6, 2, 1, 'Item 1 Sub Item 1');

INSERT INTO `TreeData` VALUES(7, 1, 3, 'Item 4');

INSERT INTO `TreeData` VALUES(8, 4, 1, 'Item 3 Sub Item 1');

INSERT INTO `TreeData` VALUES(9, 4, 2, 'Item 3 Sub Item 2');

INSERT INTO `TreeData` VALUES(10, NULL, NULL, 'Root B');

INSERT INTO `TreeData` VALUES(11, 10, 1, 'Item A');

INSERT INTO `TreeData` VALUES(12, 10, 2, 'Item B');

INSERT INTO `TreeData` VALUES(13, 10, 3, 'Item C');