MySQL递归查询树状表的子节点、父节点

来源：互联网发布：钉钉源码编辑：程序博客网时间：2024/05/16 12:37
?
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
CREATE FUNCTION `getChildList`(rootId INT)
    RETURNSvarchar(1000)
    BEGIN
      DECLAREsChildList VARCHAR(1000);
      DECLAREsChildTemp VARCHAR(1000);
      SETsChildTemp =cast(rootIdas CHAR);
      WHILE sChildTempis not null DO
        IF (sChildListis not null) THEN
          SETsChildList = concat(sChildList,',',sChildTemp);
    ELSE
      SETsChildList = concat(sChildTemp);
    ENDIF;
        SELECTgroup_concat(id) INTOsChildTemp FROM user_role where FIND_IN_SET(parentid,sChildTemp)>0;
      ENDWHILE;
      RETURNsChildList;
    END;
/*获取子节点*/
/*调用: 1、select getChildList(0) id; 2、select * 5From user_role where FIND_IN_SET(id, getChildList(2));*/
 
 
CREATE FUNCTION `getParentList`(rootId INT)
    RETURNSvarchar(1000)
    BEGIN
      DECLAREsParentList varchar(1000);
      DECLAREsParentTemp varchar(1000);
      SETsParentTemp =cast(rootIdas CHAR);
      WHILE sParentTempis not null DO
    IF (sParentListis not null) THEN
         SETsParentList = concat(sParentTemp,',',sParentList);
    ELSE
     SETsParentList = concat(sParentTemp);
    ENDIF;
        SELECTgroup_concat(parentid) INTOsParentTemp FROM user_role where FIND_IN_SET(id,sParentTemp)>0;
      ENDWHILE;
      RETURNsParentList;
    END;
/*获取父节点*/
/*调用: 1、select getParentList(6) id; 2、select * From user_role where FIND_IN_SET(id, getParentList(2));*/
0 0