项目中遇到一个需求，要求查出菜单节点的所有节点，在网上查了一下，大多数的方法用到了存储过程，由于线上环境不能随便添加存储过程。
因此在这里采用类似递归的方法对组织下的所有子节点进行查询。
创建组织表：
CREATE TABLE groups (
`group_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '组织ID',
`parent_id` int(11) DEFAULT NULL COMMENT '父节点ID',
`group_name` varchar(128) DEFAULT NULL COMMENT '组织名称',
PRIMARY KEY (`group_id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
写入数据：
INSERT INTO groups VALUES (0, null, '系统管理组织');
INSERT INTO groups VALUES (1, 0, '中国电信股份有限公司');
INSERT INTO groups VALUES (2, 1, '万州分公司');
INSERT INTO groups VALUES (3, 1, '涪陵分公司');
INSERT INTO groups VALUES (4, 2, '龙都支局');
INSERT INTO groups VALUES (5, 2, '新田支局');
INSERT INTO groups VALUES (6, 3, '马武支局');
INSERT INTO groups VALUES (7, 3, '南沱支局');
INSERT INTO groups VALUES (8, 4, '党群工作部');
INSERT INTO groups VALUES (9, 5, '客户服务部');
INSERT INTO groups VALUES (10, 6, '采购和供应链管理事业部');
INSERT INTO groups VALUES (11, 7, '网络和信息安全管理部');
树状结构：
- 系统管理组织
- 中国电信股份有限公司
- 万州分公司
- 龙都支局
- 党群工作部
- 新田支局
- 客户服务部
- 涪陵分公司
- 马武支局
- 采购和供应链管理事业部
- 南沱支局
- 网络和信息安全管理部
select
group_id,group_name
from
(
select
t1.group_id,
t1.parent_id,
t1.group_name,
t2.pids,
if(find_in_set(parent_id, @pids) > 0,@pids := concat(@pids, ',', group_id),0) as ischild
from
(select group_id,parent_id,group_name from `groups` ) t1,
(select @pids := #{groupId} as pids) t2) t3
where
ischild != 0;
比如，要查询的万州分公司下所有子节点,只需将#{groupId}变更为万州分公司的组织ID即可：
group_id|group_name|
--------+----------+
4|龙都支局 |
5|新田支局 |
8|党群工作部 |
9|客户服务部 |
t1：该子查询从groups表中选择group_id，parent_id，group_name
group_id|parent_id|group_name |
--------+---------+-----------+
0| |系统管理组织 |
1| 0|中国电信股份有限公司 |
2| 1|万州分公司 |
3| 1|涪陵分公司 |
4| 2|龙都支局 |
5| 2|新田支局 |
6| 3|马武支局 |
7| 3|南沱支局 |
8| 4|党群工作部 |
9| 5|客户服务部 |
10| 6|采购和供应链管理事业部|
11| 7|网络和信息安全管理部 |
t2：该子查询初始化一个用户定义变量@pids，并为其赋予一个名为groupId
pids|
----+
2|
if(find_in_set(parent_id, @pids) > 0,@pids := concat(@pids, ',', group_id),0)：这一部分使用find_in_set函数检查parent_id是否存在于@pids变量中。如果存在，则将当前group_id添加到@pids变量并返回；否则返回0
group_id|parent_id|group_name |pids|ischild |
--------+---------+-----------+----+---------+
0| |系统管理组织 | 2|0 |
1| 0|中国电信股份有限公司 | 2|0 |
2| 1|万州分公司 | 2|0 |
3| 1|涪陵分公司 | 2|0 |
4| 2|龙都支局 | 2|2,4 |
5| 2|新田支局 | 2|2,4,5 |
6| 3|马武支局 | 2|0 |
7| 3|南沱支局 | 2|0 |
8| 4|党群工作部 | 2|2,4,5,8 |
9| 5|客户服务部 | 2|2,4,5,8,9|
10| 6|采购和供应链管理事业部| 2|0 |
11| 7|网络和信息安全管理部 | 2|0 |
where子句过滤结果，只包括那些ischild不等于0的行
group_id|group_name|
--------+----------+
4|龙都支局 |
5|新田支局 |
8|党群工作部 |
9|客户服务部 |
引入了通用表表达式（CTE），可以使用CTE来进行递归查询
WITH RECURSIVE subordinates AS (
SELECT group_id, group_name, parent_id
FROM groups
WHERE parent_id = 2 -- 指定父节点ID
UNION ALL
SELECT g.group_id, g.group_name, g.parent_id
FROM groups g
INNER JOIN subordinates s ON s.group_id = g.parent_id
)
SELECT * FROM subordinates;
WITH RECURSIVE子句，它创建了一个名为subordinates的递归公共表达式（CTE）groups表中选择group_id，group_name和parent_id字段，其中parent_id = 2，也就是选择parent_id=2直接子组groups表（别名为'g'）与subordinates（别名为's'）进行内连接。连接条件是'g'的parent_id等于's'的group_id。这意味着我们正在查找先前找到的每个子组的子组subordinates中选择所有行
group_id|group_name|parent_id|
--------+----------+---------+
4|龙都支局 | 2|
5|新田支局 | 2|
8|党群工作部 | 4|
9|客户服务部 | 5|
@Test
public void test1() {
List<Map<String, Object>> groupList = new ArrayList<>();
groupList = queryListParentId(2,groupList);
System.out.println(groupList);
groupList.clear();
System.out.println("=====================");
List<String>list = new ArrayList<>();
list.add("3");
groupList = queryListParentId2(list,groupList);
System.out.println(groupList);
}
//方式一，循环遍历查询
public List<Map<String, Object>> queryListParentId(Integer parentId,List<Map<String, Object>> groupList) {
String sql = "select group_id,group_name from groups where parent_id = "+ parentId;
List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
if(!CollectionUtils.isEmpty(list)){
groupList.addAll(list);
for (Map<String, Object> map : list){
queryListParentId((Integer) map.get("group_id"),groupList);
}
}
return groupList;
}
//方式二，使用find_in_set函数
public List<Map<String, Object>> queryListParentId2(List<String> parentId,List<Map<String, Object>> groupList) {
String join = String.join(",", parentId);
String sql = "select group_id,group_name from groups where find_in_set(parent_id,'"+ join+"')";
List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
if(!CollectionUtils.isEmpty(list)){
groupList.addAll(list);
List<String> collect = list.stream().map(map -> map.get("group_id")+"").collect(Collectors.toList());
queryListParentId2(collect,groupList);
}
return groupList;
}
[{group_id=4, group_name=龙都支局}, {group_id=5, group_name=新田支局}, {group_id=8, group_name=党群工作部}, {group_id=9, group_name=客户服务部}]
=====================
[{group_id=6, group_name=马武支局}, {group_id=7, group_name=南沱支局}, {group_id=10, group_name=采购和供应链管理事业部}, {group_id=11, group_name=网络和信息安全管理部}]
