在MySQL中基于路径进行选择

Question

在MySQL中基于路径进行选择

5

我有一个列id，一个列parent和一个列path，其中path是一个实现了物化路径的列。

它看起来像这样：

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 | 19\20  
21 | 19 | 19\21

我需要基于这个表格进行一些查询。

我需要执行的查询有：

选择所有id为9的子元素

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

在你将ID替换为1或19时，会出现问题，因为开头没有/。

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

选择所有以1结尾的数字行，如1、11、21、31、211等。

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

以下是需要翻译的内容：

对于第1行或第19行，都能正确处理

所以最好的查询语句是：SELECT * FROM tester WHERE 'path' LIKE '1/%' OR 'path' LIKE '%/1/%'；
你有什么建议吗？

选择9的直接子元素而不包括子子孙孙
对于这个问题，Select * from tester where 'parent' = 9 就可以解决。

选择9的所有儿子，直到第x层

我的目标是要得到一行 level1, level2, level3, ... levelx 或者 x 行数据，代表不同的层级。

让我们假设这里的x是3，那么这个例子的结果将是 9, 8, 6（如果要求第4层，结果将是3）

有什么想法吗？

编辑

#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;

选择9的子代ID，向下x级，其中级别相对于9，

因此，我们将再次使用3作为x。

我们要获取以下内容：

我完全不知道如何做到这一点。

编辑：

#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;

- Hailwood

请问您能否详细解释一下“9的子级聚合计数，深度为x”的含义？ - The Scrum Meister

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- The Scrum Meister · Accepted Answer

提醒一下，这些解决方案基于字符串比较，未经过优化且无法使用索引。您应该考虑以不同的方式规范化表格。（请参见在MySQL中管理分层数据）

关于一些问题：

选择所有ID为9的子级：

由于路径列不包括前导和尾随斜杠，因此需要将它们与路径连接起来。

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

选择9的子节点的聚合计数，深度为x级：

我们需要按路径中斜杠的数量分组，减去父路径中斜杠的数量：

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

为了简化，我使用上面的查询来展示所有层级。如果您想要限制到x个层级，请使用下面的查询中的WHERE谓词。

选择9的子代id，一直到第x层，并相对于9显示其级别： 我们搜索Path列，同时考虑父级别，最多搜索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/%'

我们正在采取的步骤：

我们需要找出父级目录有多深，可以通过计算父级路径中斜杠的数量来确定。（LENGTH(p.Path) - LENGTH(REPLACE(p.Path, '/', ''))）
我们需要在该数字上加1，因为只有一个斜杠的路径深度为2级。
我们添加所需级别的x个数字。
获取到级别总数之前的路径列（使用SUBSTRING_INDEX函数）。
添加前导和尾随斜杠。
搜索最终字符串中的9。