背景
項(xiàng)目中遇到一個需求，要求查出菜單節(jié)點(diǎn)的所有節(jié)點(diǎn)，在網(wǎng)上查了一下，大多數(shù)的方法用到了存儲過程，由于線上環(huán)境不能隨便添加存儲過程。
因此在這里采用類似遞歸的方法對組織下的所有子節(jié)點(diǎn)進(jìn)行查詢。
準(zhǔn)備
創(chuàng)建組織表：
CREATE TABLE groups (
`group_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '組織ID',
`parent_id` int(11) DEFAULT NULL COMMENT '父節(jié)點(diǎn)ID',
`group_name` varchar(128) DEFAULT NULL COMMENT '組織名稱',
PRIMARY KEY (`group_id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
寫入數(shù)據(jù)：
INSERT INTO groups VALUES (0, null, '系統(tǒng)管理組織');
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, '客戶服務(wù)部');
INSERT INTO groups VALUES (10, 6, '采購和供應(yīng)鏈管理事業(yè)部');
INSERT INTO groups VALUES (11, 7, '網(wǎng)絡(luò)和信息安全管理部');
樹狀結(jié)構(gòu)：
- 系統(tǒng)管理組織
- 中國電信股份有限公司
- 萬州分公司
- 龍都支局
- 黨群工作部
- 新田支局
- 客戶服務(wù)部
- 涪陵分公司
- 馬武支局
- 采購和供應(yīng)鏈管理事業(yè)部
- 南沱支局
- 網(wǎng)絡(luò)和信息安全管理部
實(shí)現(xiàn)
查詢
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;
比如，要查詢的萬州分公司下所有子節(jié)點(diǎn),只需將#{groupId}變更為萬州分公司的組織ID即可：
group_id|group_name|
--------+----------+
4|龍都支局 |
5|新田支局 |
8|黨群工作部 |
9|客戶服務(wù)部 |
語句解析
-
t1：該子查詢從groups表中選擇group_id，parent_id，group_name
group_id|parent_id|group_name |
--------+---------+-----------+
0| |系統(tǒng)管理組織 |
1| 0|中國電信股份有限公司 |
2| 1|萬州分公司 |
3| 1|涪陵分公司 |
4| 2|龍都支局 |
5| 2|新田支局 |
6| 3|馬武支局 |
7| 3|南沱支局 |
8| 4|黨群工作部 |
9| 5|客戶服務(wù)部 |
10| 6|采購和供應(yīng)鏈管理事業(yè)部|
11| 7|網(wǎng)絡(luò)和信息安全管理部 |
-
t2：該子查詢初始化一個用戶定義變量@pids，并為其賦予一個名為groupId
pids|
----+
2|
-
if(find_in_set(parent_id, @pids) > 0,@pids := concat(@pids, ',', group_id),0)：這一部分使用find_in_set函數(shù)檢查parent_id是否存在于@pids變量中。如果存在，則將當(dāng)前group_id添加到@pids變量并返回；否則返回0
group_id|parent_id|group_name |pids|ischild |
--------+---------+-----------+----+---------+
0| |系統(tǒng)管理組織 | 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|客戶服務(wù)部 | 2|2,4,5,8,9|
10| 6|采購和供應(yīng)鏈管理事業(yè)部| 2|0 |
11| 7|網(wǎng)絡(luò)和信息安全管理部 | 2|0 |
-
使用 where子句過濾結(jié)果，只包括那些ischild不等于0的行
group_id|group_name|
--------+----------+
4|龍都支局 |
5|新田支局 |
8|黨群工作部 |
9|客戶服務(wù)部 |
MySQL 8.0版本
引入了通用表表達(dá)式（CTE），可以使用CTE來進(jìn)行遞歸查詢
WITH RECURSIVE subordinates AS (
SELECT group_id, group_name, parent_id
FROM groups
WHERE parent_id = 2 -- 指定父節(jié)點(diǎn)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子句，它創(chuàng)建了一個名為subordinates的遞歸公共表達(dá)式（CTE） -
從 groups表中選擇group_id，group_name和parent_id字段，其中parent_id = 2，也就是選擇parent_id=2直接子組 -
將 groups表（別名為'g'）與subordinates（別名為's'）進(jìn)行內(nèi)連接。連接條件是'g'的parent_id等于's'的group_id。這意味著我們正在查找先前找到的每個子組的子組 -
從 subordinates中選擇所有行
group_id|group_name|parent_id|
--------+----------+---------+
4|龍都支局 | 2|
5|新田支局 | 2|
8|黨群工作部 | 4|
9|客戶服務(wù)部 | 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);
}
//方式一，循環(huán)遍歷查詢
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函數(shù)
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=客戶服務(wù)部}]
=====================
[{group_id=6, group_name=馬武支局}, {group_id=7, group_name=南沱支局}, {group_id=10, group_name=采購和供應(yīng)鏈管理事業(yè)部}, {group_id=11, group_name=網(wǎng)絡(luò)和信息安全管理部}]
