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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
| -- 递归函数适配（方天琦）
/**
create table a_test
( parentid varchar(10),
subid varchar(10));
insert into a_test values ( '1', '2' );
insert into a_test values ( '1', '3' );
insert into a_test values ( '2', '4' );
insert into a_test values ( '2', '5' );
insert into a_test values ( '3', '6' );
insert into a_test values ( '3', '7' );
insert into a_test values ( '5', '8' );
insert into a_test values ( '5', '9' );
insert into a_test values ( '7', '10' );
insert into a_test values ( '7', '11' );
insert into a_test values ( '10', '12' );
insert into a_test values ( '10', '13' );
select * from a_test;
-- 测试：
-- select parentid,subid,level from A_TEST start with subid='7' connect by subid= prior parentid order by level desc;
select connectByPrior('A_TEST','p',false,'7');-- 3,7
-- select parentid,subid,level from A_TEST start with parentid='7' connect by subid= prior parentid order by level desc;
select connectByPrior('A_TEST','p',true,'7');-- 3,7,11,10
-- select parentid,subid,level from A_TEST start with subid='7' connect by prior subid= parentid order by level desc;
select connectByPrior('A_TEST','c',false,'7');-- 7,10,11,12,13
-- select parentid,subid,level from A_TEST start with parentid='7' connect by prior subid= parentid order by level desc;
select connectByPrior('A_TEST','c',true,'7'); -- 10,11,12,13
@param tablename 表名
@param direction prior 后面是父节点：p ，子节点：c
@param isparentid start with 后面的参数是否是父节点
@param rootid start with 后面的参数的值
@return text 返回子节点的列表
*/
drop function if exists connectByPrior;
create function connectByPrior(tablename varchar(100),direction varchar(8),isparentid BOOLEAN ,rootid text)
returns text
begin
DECLARE v_child_list text;
DECLARE v_parent_list text;
DECLARE v_child text;
DECLARE v_parent text;
set v_child_list=null;
set v_parent_list=null;
set rootid = replace(rootid,'\'','');
if (lower(tablename) = 'a_test' and isparentid) then
select group_concat(subid) into rootid from a_test where find_in_set(parentid,rootid)>0;
end if;
set v_child=rootid;
set v_parent=rootid;
while v_child is not null or v_parent is not null do
set v_child_list=concat_ws(',',v_child_list,v_child);
set v_parent_list=concat_ws(',',v_parent_list,v_parent);
if(lower(tablename) = 'a_test') then
if(direction = 'p') then
select group_concat(parentid) into v_child from a_test where find_in_set(subid,v_child)>0;
select group_concat(subid) into v_parent from a_test where find_in_set(subid,v_child)>0;
else
set v_parent = null;
select group_concat(subid) into v_child from a_test where find_in_set(parentid,v_child)>0;
end if;
else
set v_child = null;
set v_parent = null;
end if;
end while;
if (direction = 'p') then
return v_parent_list;
end if;
return v_child_list;
end;
|