当前位置: 首页 > 面试题库 >

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

洪安顺
2023-03-14
问题内容

我有一个使用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');

问题答案:
SELECT d.`iD`, d.`subsectionOf`,
       CONCAT(REPEAT('-', p.`len`), d.`name`) as hier,
       p.`len`, p.`ancestor`, p.`descendant`,
       GROUP_CONCAT(crumbs.`ancestor`) AS breadcrumbs
FROM `TreeData` AS d
JOIN `TreePaths` AS p ON d.`iD` = p.`descendant`
JOIN `TreePaths` AS crumbs ON crumbs.`descendant` = p.`descendant`
WHERE p.`ancestor` = 1
GROUP BY d.`iD`
ORDER BY breadcrumbs;

+----+--------------+---------------------+-----+----------+------------+-------------+
| iD | subsectionOf | hier                | len | ancestor | descendant | breadcrumbs |
+----+--------------+---------------------+-----+----------+------------+-------------+
|  1 |         NULL | Root A              |   0 |        1 |          1 | 1           | 
|  2 |            1 | -Item 1             |   1 |        1 |          2 | 1,2         | 
|  5 |            2 | --Item 1 Sub Item 2 |   2 |        1 |          5 | 1,2,5       | 
|  6 |            2 | --Item 1 Sub Item 1 |   2 |        1 |          6 | 1,2,6       | 
|  3 |            1 | -Item 2             |   1 |        1 |          3 | 1,3         | 
|  4 |            1 | -Item 3             |   1 |        1 |          4 | 1,4         | 
|  8 |            4 | --Item 3 Sub Item 1 |   2 |        1 |          8 | 1,4,8       | 
|  9 |            4 | --Item 3 Sub Item 2 |   2 |        1 |          9 | 1,4,9       | 
|  7 |            1 | -Item 4             |   1 |        1 |          7 | 1,7         | 
+----+--------------+---------------------+-----+----------+------------+-------------+


 类似资料:
  • 问题内容: 我正在尝试从PDF文件提取图像。我在网上找到了一个很好的例子: 那给了我所有的图像,但是图像顺序错误。我的下一个尝试如下所示: 尽管o.isStream()== true,但是我只得到/ Length和/ Filter,流只有大约100个字节长。完全找不到图像。 我的问题是,正确的方式是从PDF文件中获取所有图像的正确方法。 问题答案: 我在其他地方找到了答案,即iText邮件列表。

  • 问题内容: 我正在尝试从Firebase订购数据,因此最近的帖子位于顶部(如Instagram),但我无法使其正常运行。我应该使用服务器时间戳吗?是否有一个“ createdAt”字段? 问题答案: 仅对数组使用 reverse() 不足以包含所有内容。您需要考虑不同的事情: 在检索数据时进行限制,请使用 append() ,然后使用 reverse() 可以节省时间。您不需要每次都删除所有数组。

  • 问题内容: 我正在创建一个寻找食谱的应用程序,基本上,我有一个“收藏夹”,如果用户收藏了一个食谱,他们将可以在应用程序的“收藏夹”选项卡中轻松访问它。基本上这是我现在的结构: 基本上,每当他们转到“收藏夹”选项卡时,我都需要所有配方ID的列表,以便可以进行API调用来检索配方信息。我基本上是在浏览字典。我还希望能够允许用户取消收藏该食谱,因此将其从数据库中删除。如果使用以下方法,如何将其删除: 添

  • 如果我使用,是否总是100%保证我将获得的数据是从最早到最新的排序,或者键是按照词典的顺序排列的?

  • 问题内容: 这是一个jQuery Mobile问题,但它也与纯jQuery有关。 如何在不将页面转换到设置为表单操作属性的页面的情况下发布表单数据。我正在构建phonegap应用程序,并且我不想直接访问服务器端页面。 我尝试了几个示例,但是每次表单都将我转发到目标php文件。 问题答案: 介绍 本示例是使用jQuery Mobile 1.2创建的。如果你想看到最近的例子然后看看这个 文章 或者这个

  • 问题内容: 我正在使用derby作为嵌入式数据库。此外,我在单元测试中使用了它的内存数据库选项。 我不知道如何正确关闭(快速查看代码) Derby数据库。我相信我有它适用于标准数据库,但是在内存数据库中尝试类似代码时遇到了不同的异常。 我将省略细节,如果需要其他感觉,我将添加它们。 基本上,我试图以以下两种方式关闭数据库,在这种方式中,内存数据库始终被称为“ eh”: 然后: 前者导致例外,但例外