有如下树形结构：RT-ST-SST-SSST共四层，RT是根节点，往后依次是一代子节点，二代子节点，三代子节点。 如何根据当前节点的id，获得其子节点呢?这是一个SQL问题。加入传入的id为1（即根节点），使用自连 SUBSTRING_INDEX函数得到其子节点：
示例：
代码语言：javascript复制id name type url
1 大树 RT root树形结构视图：
代码语言：javascript复制--大树
--树干1
--树枝1
--树叶1
--树叶2
--树枝2
--树干2
--树枝3
--树叶3建表SQL：
代码语言：javascript复制create table tree_node(id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(200), type VARCHAR(30),url VARCHAR(200));插入基础数据SQL：
代码语言：javascript复制insert into tree_node(name, type, url) values('大树', 'RT', 'root');
insert into tree_node(name, type, url) values('树干1', 'ST', 'root/tree_main_line1');
insert into tree_node(name, type, url) values('树干2', 'ST', 'root/tree_main_line2');
insert into tree_node(name, type, url) values('树枝1', 'SST', 'root/tree_main_line1/tree_branche1');
insert into tree_node(name, type, url) values('树枝2', 'SST', 'root/tree_main_line1/tree_branche2');
insert into tree_node(name, type, url) values('树枝3', 'SST', 'root/tree_main_line2/tree_branche3');
insert into tree_node(name, type, url) values('树叶1', 'SSST', 'root/tree_main_line1/tree_branche1/tree_leaf1');
insert into tree_node(name, type, url) values('树叶2', 'SSST', 'root/tree_main_line1/tree_branche1/tree_leaf2');
insert into tree_node(name, type, url) values('树叶3', 'SSST', 'root/tree_main_line2/tree_branche3/tree_leaf3');查看刚刚插入的数据：
代码语言：javascript复制mysql> select * from tree_node;
---- --------- ------ -----------------------------------------------
| id | name | type | url |
---- --------- ------ -----------------------------------------------
| 1 | 大树 | RT | root |
| 2 | 树干1 | ST | root/tree_main_line1 |
| 3 | 树干2 | ST | root/tree_main_line2 |
| 4 | 树枝1 | SST | root/tree_main_line1/tree_branche1 |
| 5 | 树枝2 | SST | root/tree_main_line1/tree_branche2 |
| 6 | 树枝3 | SST | root/tree_main_line2/tree_branche3 |
| 7 | 树叶1 | SSST | root/tree_main_line1/tree_branche1/tree_leaf1 |
| 8 | 树叶2 | SSST | root/tree_main_line1/tree_branche1/tree_leaf2 |
| 9 | 树叶3 | SSST | root/tree_main_line2/tree_branche3/tree_leaf3 |
---- --------- ------ ----------------------------------------------- 加入传入的id为1（即根节点），使用自连 SUBSTRING_INDEX函数得到其子节点：
代码语言：javascript复制mysql> select tree1.* from tree_node as tree1
-> join tree_node as tree2
-> on SUBSTRING_INDEX(tree1.url,'/',1) = tree2.url
-> and (length(tree1.url) - length(replace(tree1.url, '/', ''))) = 1
-> where tree2.id = 1;
---- --------- ------ ----------------------
| id | name | type | url |
---- --------- ------ ----------------------
| 2 | 树干1 | ST | root/tree_main_line1 |
| 3 | 树干2 | ST | root/tree_main_line2 |
---- --------- ------ ---------------------- 解析SQL：
代码语言：javascript复制select tree1.* from tree_node as tree1
join tree_node as tree2
on SUBSTRING_INDEX(tree1.url,'/',1) = tree2.url
and (length(tree1.url) - length(replace(tree1.url, '/', ''))) = 1
where tree2.id = 1;
##返回"树干1"的父节点的url
select SUBSTRING_INDEX('root/tree_main_line1','/',1)
##将"/"替换为""
mysql> select replace('root/tree_main_line1', '/', '');
------------------------------------------
| replace('root/tree_main_line1', '/', '') |
------------------------------------------
| roottree_main_line1 |
------------------------------------------
##利用length差获得"/"的个数为1，则认为是子节点
mysql> select length('root/tree_main_line1') - length(replace('root/tree_main_line1', '/', ''));
-----------------------------------------------------------------------------------
| length('root/tree_main_line1') - length(replace('root/tree_main_line1', '/', '')) |
-----------------------------------------------------------------------------------
| 1 |
----------------------------------------------------------------------------------- 
