常用SQL笔记MySQL
2025-04-08 23:55阅读:82评论:0
MySQL之递归查询
分类递归查询子节点 分类递归查询父级节点,SQL1 分类递归查询父级节点,SQL2 select GROUP\CONCAT(pid) into tempids from tbl\category where FIND\IN\SET(id,tempids)>0;递归查询子节点还是父节点关键语句 查询子
_PROTECTED0__
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
delimiter $$ drop function if exists get_child_categoryt$$ create function get_child_categoryt(in_id varchar(10)) returns varchar(1000) begin declare ids varchar(1000) default ''; declare tempids varchar(1000); set tempids = in_id; while tempids is not null do set ids = CONCAT_WS(',',ids,tempids); select GROUP_CONCAT(id) into tempids from tbl_category where FIND_IN_SET(pid,tempids)>0; end while; return ids; end $$ delimiter ; select get_child_categoryt(1003) SELECT * FROM tbl_category WHERE FIND_IN_SET(id, get_child_list(1003)) _PROTECTED0__
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
delimiter $$ drop function if exists get_child_categoryt_test$$ create function get_child_categoryt_test(in_id varchar(10)) returns varchar(1000) begin declare ids varchar(1000) default ''; declare tempids varchar(1000); set tempids = in_id; while tempids is not null do set ids = CONCAT_WS(',',ids,tempids); select GROUP_CONCAT(pid) into tempids from tbl_category where FIND_IN_SET(id,tempids)>0; end while; return ids; end $$ delimiter ; select get_child_categoryt_test(1003) SELECT * FROM tbl_category WHERE FIND_IN_SET(id, get_child_categoryt_test(1003)) _PROTECTED0__
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT t2.id FROM ( SELECT @r AS _id, ( SELECT @r := pid FROM tbl_category WHERE id = _id ) AS parent_id, @s := @s + 1 AS sort FROM ( SELECT @r := 1003, @s := 0 ) temp, tbl_category WHERE @r > 0 ) t1 JOIN tbl_category t2 ON t1._id = t2.id ORDER BY t1.sort DESC select GROUP\__PROTECTED_1__category where FIND\__PROTECTED_2__SET(id,tempids)>0;递归查询子节点还是父节点关键语句
- 查询子节点select GROUP\__PROTECTED_3__category where FIND\__PROTECTED_4__SET(pid,tempids)>0;
- 查询父节点select GROUP\__PROTECTED_5__category where FIND\__PROTECTED_6__SET(id,tempids)>0;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
DROP TABLE IF EXISTS `tbl_category`; CREATE TABLE `tbl_category` ( `id` bigint(20) NOT NULL, `name` varchar(255) NOT NULL, `pid` bigint(20) NOT NULL, PRIMARY KEY (`id`)) INSERT INTO `tbl_category`(`id`, `name`, `pid`) VALUES ('1000', '总公司', 0); INSERT INTO `tbl_category`(`id`, `name`, `pid`) VALUES ('1001', '北京分公司', '1000'); INSERT INTO `tbl_category`(`id`, `name`, `pid`) VALUES ('1002', '上海分公司', '1000'); INSERT INTO `tbl_category`(`id`, `name`, `pid`) VALUES ('1003', '北京研发部', '1001'); INSERT INTO `tbl_category`(`id`, `name`, `pid`) VALUES ('1004', '北京财务部', '1001'); INSERT INTO `tbl_category`(`id`, `name`, `pid`) VALUES ('1005', '北京市场部', '1001'); INSERT INTO `tbl_category`(`id`, `name`, `pid`) VALUES ('1006', '北京研发一部', '1003'); INSERT INTO `tbl_category`(`id`, `name`, `pid`) VALUES ('1007', '北京研发二部', '1003'); INSERT INTO `tbl_category`(`id`, `name`, `pid`) VALUES ('1008', '北京研发一部一小组', '1006'); INSERT INTO `tbl_category`(`id`, `name`, `pid`) VALUES ('1009', '北京研发一部二小组', '1006'); INSERT INTO `tbl_category`(`id`, `name`, `pid`) VALUES ('1010', '北京研发二部一小组', '1007'); INSERT INTO `tbl_category`(`id`, `name`, `pid`) VALUES ('1011', '北京研发二部二小组', '1007'); INSERT INTO `tbl_category`(`id`, `name`, `pid`) VALUES ('1012', '北京市场一部', '1005'); INSERT INTO `tbl_category`(`id`, `name`, `pid`) VALUES ('1013', '上海研发部', '1002'); INSERT INTO `tbl_category`(`id`, `name`, `pid`) VALUES ('1014', '上海研发一部', '1013'); INSERT INTO `tbl_category`(`id`, `name`, `pid`) VALUES ('1015', '上海研发二部', '1013'); SELECT * FROM tbl_category WHERE FIND_IN_SET(id, get_child_categoryt$$(1003)) _PROTECTED0__
sql
1
2
3
4
5
6
7
SELECT au.id FROM (SELECT * FROM tbl_corp_dept WHERE parent_id IS NOT NULL) au, (SELECT @pid := 10) pd WHERE FIND_IN_SET(parent_id, @pid) > 0 AND @pid := concat(@pid, ',', id) union select id from tbl_corp_dept where FIND_IN_SET(id,@pid) > 0; --- 如果不加这行则不包括自身