MySQL Looped Join如何测试结果是否完整? [英] How to do MySQL Looped Join which tests if results are complete?

问题描述

情况:
我有一个mysql目录表.每个目录都有一个父目录(存储为parentID)，直到根目录的parentID为0.

Situation:
I have a mysql table of directories. Each directory has a parent directory (stored as parentID), up to the point where the root directory has a parentID of 0.

例如:

rowID: 1, name: Dir1,    parentID: 0 (root directory)
rowID: 2, name: Dir2,    parentID: 0 (root directory)
rowID: 3, name: Subdir1, parentID: 1 (lives in "Dir1")
rowID: 4, name: Subdir2, parentID: 1 (lives in "Dir1")
rowID: 5, name: Subdir3, parentID: 3 (lives in "Subdir1", which in turn lives in "Dir1")
rowID: 6, name: Subdir4, parentID: 5 (lives in "Subdir3", which lives in "Subdir1", which lives in "Dir1")

所以这里有3个目录深度结构.

So here there is a 3 directory depth structure.

我需要构建一条语句，将任何目录连接到其父目录，并继续这样做，直到最后一个连接目录的父目录ID为0(即找到根目录).您可以想到它，就好像在任何目录下都可以找到返回到父级的面包屑一样.

I need to build a statement which joins any directory to its parent and continues to do so until the last directory joined has a parentID of 0 (i.e. found the root directory). You can think of it as if, given any directory, you can find the breadcrumb back to the parent.

我认为这可能需要进行一些MySQL循环，但是为了我的生命，我无法使用任何网络示例.我什至无法运行某些示例，因为它们似乎存在某种语法错误.有人可以帮助我入门吗?

I figure that this may require some MySQL looping but for the life of me, I can't get any of the web examples to work. I can't even get some of the examples to run as they seem to have some sort of syntax errors in them. Can anyone help me get started?

我可以接受任何最简单的结果格式并提供最佳性能来完成此操作.以正确的顺序排列一个简单的行号数组(例如5、3、1、0，表示获得ID为0的步骤)，或者是一个完整的表(最佳)，它将是达到此目的的行的有序列表，例如

I can accept any result format that's easiest and gives best performance to get this done. Either a simple array of row numbers in correct order (e.g. 5, 3, 1, 0, indicating the steps to get to ID of 0), or a full table (best) which will be an ordered list of rows that achieve this, e.g.

rowID: 5, name: Subdir3, parentID: 2;
rowID: 3, name: Subdir1, parentID: 1;
rowId: 1, name: Dir1,    parentID: 0;

非常感谢您的帮助！

推荐答案

好吧，找到了实际部署具有与所述相似结构的简单数据库的时间.

Alright, found the time to actually deploy a simple database with a similar structure as described.

下表如下:

CREATE TABLE `t_hierarchy` (
    `rowID` INT(11) NULL DEFAULT NULL,
    `name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
    `parentID` INT(11) NULL DEFAULT NULL
);

我基本上插入了与您上面给出的完全相同的内容，但对于根/无父级，使用NULL值而不是0

I basicly inserted the exact same stuff as you have given above but used NULL values instead of 0 for root/no parent

我所做的是来自

What I've done is the quite cryptic example from http://explainextended.com/2009/07/20/hierarchical-data-in-mysql-parents-and-children-in-one-query/ . and just corrected the column names to fit mine.

由于这只会生成递归层次结构，因此我在示例中添加了一个愚蠢的联接(ad.rowID = qi.id):

Since this only generates you a recursive hierarchy, I just added a stupid join into the example ( ad.rowID = qi.id ):

 SELECT  qi.id, qi.parent, ad.rowId, ad.name, level
FROM    (
        SELECT  @r AS id,
                (
                SELECT  @r := parentID
                FROM    t_hierarchy
                WHERE   rowID = id
                ) AS parent,
                @l := @l + 1 AS level
        FROM    (
                SELECT  @r := 5, -- change this 5 to the directory ID you want to resolve
                        @l := 0,
                        @cl := 0
                ) vars,
                t_hierarchy h
        WHERE   @r <> 0
        ORDER BY
                level DESC
        ) qi, t_hierarchy ad
        WHERE ad.rowID = qi.id

这将生成以下(期望的)输出:

And this generates the follwoing (desired) output:

id父rowId名称级别

id parent rowId name level

1 NULL 1 Dir1 3

1 NULL 1 Dir1 3

3 1 3 Subdir1 2

3 1 3 Subdir1 2

5 3 5 Subdir3 1

5 3 5 Subdir3 1

级别是一个帮助程序列，它告诉您解决此问题所需要的深度". 您要做的就是将@r:=旁边的"5"更改为您要向下迭代的目录ID.

Level is a helper column which tells you how "deep" it had to resolve to reach this. All you will have to do is change the "5" next to @r := to the directory ID from where you wanna iterate down.

如果要切换方向(从上到下)，只需按级别列(在[...] WHERE ad.rowID = qi.id ORDER BY级别ASC中进行排序)

If you want to switch the direction (from up to down) simply sort by level column ([...] WHERE ad.rowID = qi.id ORDER BY level ASC )

希望这对您有所帮助.

qi.id和ad.rowID是重复项，只需删除其中一个;-)...该死，我讨厌该层次结构的东西

qi.id and ad.rowID are duplicates, just remove one of them ;-)... damn I hate that hierarchy stuff

这篇关于MySQL Looped Join如何测试结果是否完整?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！