# 前言

## 方案原理

id name parent
1 上海 中国
2 浦东 上海

``select parent from 区域表 where name = '上海' ``

``select name from 区域表 where parent = '上海' ``

``select name from 区域表 where parent in （select name from 区域表 where parent = '上海'）``

``````result_set = select name from 区域表 where parent = '上海';
current_parent = select name from 区域表 where parent = '上海';
while current_parent is not null:
current_parent = select name from 区域表 where parent in current_parent

## 方案优缺点

1. 只包含直接父子查询的场景
2. 包含多层查询，但是可以加载所有数据到内存中的场景

# 方案二、预排序遍历树算法(modified preorder tree traversal algorithm)

## 方案原理

id name lindex rindex
1 上海 16 25
2 浦东 19 24

``select rindex-lindex as region_num from 区域表 where name = '上海';``

``select name from 区域表 where lindex > 16 and rindex < 25;``

``select name from 区域表 where lindex > 16 and lindex < 25;``
``select name from 区域表 where rindex > 16 and rindex < 25;``
``select name from 区域表 where rindex > 16 and lindex < 25;``

``select name from 区域表 where rindex < 19 and lindex > 24;``

``select name from 区域表 where rindex < 19 and lindex > 19;``
``select name from 区域表 where rindex < 24 and lindex > 24;``
``select name from 区域表 where rindex < 24 and lindex > 19;``

``````update 区域表 set rindex = rindex-1 where rindex < 24 and rindex > 19;
update 区域表 set lindex = lindex-1 where lindex < 24 and lindex > 19;
update 区域表 set rindex = rindex-2 where rindex > 24;
update 区域表 set lindex = lindex-2 where lindex > 24;``````

``````update 区域表 set rindex = rindex+1 where rindex < 24 and rindex >= 19;
update 区域表 set lindex = lindex+1 where lindex < 24 and lindex >= 19;
update 区域表 set rindex = rindex+2 where rindex >= 24;
update 区域表 set lindex = lindex+2 where lindex >= 24;``````

1. 数据几乎不更新的场景。

# 方案三、路径枚举法（Path Enumerations）

## 方案原理

id name path
1 上海 中国/
2 浦东 中国/上海

``select name from 区域表 where path like '中国/上海/%';``

``select name from 区域表 where path like '%/上海';``

# 方案四、ClosureTable

## 方案原理

id child parent deepth
1 孙桥 浦东 1
2 孙桥 上海 2
3 孙桥 中国 3
4 浦东 上海 1
5 浦东 中国 2
6 上海 中国 1

``select child from 区域表 where parent = '上海' and deepth = 1;``

``select child from 区域表 where parent = '上海';``

``select parent from 区域表 where child = '上海' and deepth = 1;``

``select parent from 区域表 where child = '上海';``

``````parents = select parent from 区域表 where child = '上海';
children = select child from 区域表 where parent = '上海';
update 区域表 set depth = depth -1 where parent in parents and child in children.
delete parent from 区域表 where child = '上海' or parent = '上海';``````