在mysql中基于路径进行选择

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

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

/

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

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

SELECT * FROM

WHERE \'path\' LIKE \'1/%\' OR \'path\' LIKE \'%/1/%\'

where \'parent\' = 9

9, 8, 6

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

10 | 1
11 | 2
18 | 2
12 | 3
13 | 3
14 | 3 

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