Question

页: 1

它希望

1  | N | 1  
2  | 1  | 1/2  
3  | 2  | 1/2/3  
4  | 3  | 1/2/3/4  
5  | 3  | 1/2/3/5  
6  | 2  | 1/2/6  
7  | 6  | 1/2/6/7  
8  | 2  | 1/2/8  
9  | 1  | 1/9  
10 | 9  | 1/9/10  
11 | 10 | 1/9/10/11  
12 | 11 | 1/9/10/11/12  
13 | 11 | 1/9/10/11/13  
14 | 11 | 1/9/10/11/14  
15 | 14 | 1/9/10/11/14/15  
16 | 14 | 1/9/10/11/14/16  
17 | 14 | 1/9/10/11/14/17  
18 | 10 | 1/9/10/18  
19 | N | 19  
20 | 19 | 1920  
21 | 19 | 1921

我需要根据本表提出一些问题。

The queries I need to do are

Select all children of id 9

SELECT * FROM `tester` WHERE  path  LIKE  %/9/% ;

罚款。在您以1或19条取代该身份证之前,先没有<代码>/。

SELECT * FROM `tester` WHERE  path  LIKE  %1/% ;

选择1、1、11、21、31、211等数字结尾的所有行文

SELECT * FROM `tester` WHERE  path  LIKE  1/% ;

第1行或第19行将正确运作

So SELECT * FROMtesterWHERE path LIKE 1/% OR path LIKE %/1/% ;
Is the best I can come up with, any suggestions?

Select Direct children of 9 but not sub-children
For this Select * fromtesterwhere parent = 9; will work fine.

select an aggregate count of 9 s children, x levels deep.

因此,在结束发言时,我要提到代表不同层次的<条码>第1级、第2级、第3级、第......级x/<条码>或x行,

Let us pretend x is 3 for this example The rows from this example would be 9, 8, 6 (the 4th level if we requested it would be 3)

任何想法?

<><>Edit>/strong>

#select count of children of specific node(5) down to a maximum of three levels, do no include the parent
SELECT COUNT(child.id) children, 
LENGTH(REPLACE(child.path, parent.path,   )) - LENGTH(REPLACE(REPLACE(child.path, parent.path,   ),  / ,   )) AS LEVEL
FROM `tester` child JOIN `tester` parent ON child.path LIKE CONCAT(parent.path, % ) 
WHERE parent.id  =5 
GROUP BY LEVEL HAVING LEVEL <= 3 AND LEVEL > 0;

**select 9 s children s id s down to x levels, with the level relative to 9,

因此,我们再一次将使用3×。

我们正期待着退席。

我再次对如何做到这一点感到完全损失。

Edit:

#select all information, and relative level from parent of children of specific node(5) down to a maximum of three levels, do no include the parent
SELECT child.*, 
LENGTH(REPLACE(child.path, parent.path,   )) - LENGTH(REPLACE(REPLACE(child.path, parent.path,   ),  / ,   )) AS LEVEL
FROM `tester` child JOIN `tester` parent ON child.path LIKE CONCAT(parent.path, % ) 
WHERE parent.id  =9 
GROUP BY id HAVING LEVEL <= 3 AND LEVEL > 0;

Answer 1

仅仅给你一个头盔,这些解决办法基于细微的比较,并不优化,也不使用指数。您应考虑以不同方式使表格正常化。 (见管理等级 MySQL的数据

关于一些问题:

www.un.org/Depts/DGACM/index_spanish.htm 选择所有9岁儿童:

自<代码>Path 第1栏不包括铅和amp;拖拉lash,你需要把lash塞到:

SELECT * 
FROM tester
WHERE CONCAT( / , path,  / ) LIKE  %/9/% ;

http://www.ohchr.org。

我们需要根据路边的斜坡数,减去母体道路上的冲突次数:

SELECT (LENGTH(c.Path) - LENGTH(REPLACE(c.Path,  / ,   )))
    - (LENGTH(p.Path) - LENGTH(REPLACE(p.Path,  / ,   ))) AS Level,
    COUNT(*)
FROM tester c
    JOIN tester p ON c.Parent = p.ID
WHERE CONCAT( / , path,  / ) LIKE  %/9/% ;
GROUP BY 1

简便i利用上述询问显示所有水平,如果你想要将X级深度限制在外,则使用<代码>。 WHERE 依次查询。

select 9 s children s id s down to x levels, with the level relative to 9:

在考虑父母的等级时,我们检索的栏目,最高可达到X级:

SELECT c.*
FROM tester c
    JOIN tester p ON c.Parent = p.ID
WHERE CONCAT(
     / ,
    SUBSTRING_INDEX(
        Path, 
         / , 
        (LENGTH(p.Path) - LENGTH(REPLACE(p.Path,  / ,   ))) + 4
    ),
 / ) LIKE  %/9/%

我们正在采取的步骤:

We need to find out how deep the parent is, we can find that by counting the slashes in the parent s path. (LENGTH(p.Path) - LENGTH(REPLACE(p.Path, / , )))
We need to add 1 to that number, since a path with 1 slash is 2 levels deep.
We add the x number of desired levels.
Grab the path column up to the level total, (Use the SUBSTRING_INDEX function).
Add the leading and trailing slash.
Search the final string for 9.

友情链接