背景
項目中遇到一個需求，要求查出菜單節點的所有節點，在網上查了一下，大多數的方法用到了存儲過程，由于線上環境不能隨便添加存儲過程。
因此在這里采用類似遞歸的方法對組織下的所有子節點進行查詢。
準備
創建組織表：
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|客戶服務部 |
MySQL 8.0版本
引入了通用表表達式（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=網絡和信息安全管理部}]
