SQL 递归表

Question

我有以下表格，groups 表格包含分层排序的组和 group_member 存储用户所属的组。

groups
---------
id  
parent_id
name

group_member
---------
id
group_id
user_id

ID  PARENT_ID  NAME
---------------------------
1   NULL       Cerebra
2   1          CATS 
3   2          CATS 2.0 
4   1          Cerepedia 
5   4          Cerepedia 2.0
6   1          CMS 

ID GROUP_ID USER_ID
---------------------------
1  1        3
2  1        4
3  1        5
4  2        7
5  2        6
6  4        6
7  5        12
8  4        9
9  1        10

我想检索给定用户的可见组。也就是说，用户所属的组和这些组的子组。例如，上面的数据:

USER  VISIBLE_GROUPS
9     4, 5 
3     1,2,4,5,6
12    5 

我正在使用递归和多个数据库查询获取这些值。但我想知道是否可以通过单个 SQL 查询来提高我的应用程序性能。我正在使用 MySQL。

Answer 1

我想到了两件事:

1 - 您可以重复将表外连接到自身以递归地向上遍历您的树，如:

SELECT *
FROM
  MY_GROUPS MG1
 ,MY_GROUPS MG2
 ,MY_GROUPS MG3
 ,MY_GROUPS MG4
 ,MY_GROUPS MG5
 ,MY_GROUP_MEMBERS MGM
WHERE MG1.PARENT_ID = MG2.UNIQID (+)
  AND MG1.UNIQID = MGM.GROUP_ID (+)
  AND MG2.PARENT_ID = MG3.UNIQID (+)
  AND MG3.PARENT_ID = MG4.UNIQID (+)
  AND MG4.PARENT_ID = MG5.UNIQID (+)
  AND MGM.USER_ID = 9

这会给你这样的结果:

UNIQID PARENT_ID NAME      UNIQID_1 PARENT_ID_1 NAME_1 UNIQID_2 PARENT_ID_2 NAME_2  UNIQID_3 PARENT_ID_3 NAME_3 UNIQID_4 PARENT_ID_4 NAME_4 UNIQID_5 GROUP_ID USER_ID
4      2         Cerepedia 2        1           CATS   1        null        Cerebra null     null        null   null      null       null   8        4        9

这里的限制是你必须为你想在树上走的每个“级别”添加一个新的连接。如果您的树少于 20 个级别，那么您可以通过创建一个显示每个用户的 20 个级别的 View 来摆脱它。

2 - 据我所知，唯一的其他方法是创建一个递归数据库函数，然后从代码中调用它。这样您仍然会有一些查找开销(即，您的查询数量仍然等于您在树上行走的级别数量)，但总体而言它应该更快，因为它全部发生在数据库中。

我不确定 MySql，但在 Oracle 中，这样的函数与这个类似(你必须更改表和字段名称；我只是复制我过去所做的一些事情):

CREATE OR REPLACE FUNCTION GoUpLevel(WO_ID INTEGER, UPLEVEL INTEGER) RETURN INTEGER
IS
BEGIN
  DECLARE
    iResult INTEGER;
    iParent INTEGER;
BEGIN
  IF UPLEVEL <= 0 THEN
    iResult := WO_ID;
  ELSE
    SELECT PARENT_ID
    INTO iParent
    FROM WOTREE
    WHERE ID = WO_ID;    
    iResult := GoUpLevel(iParent,UPLEVEL-1);  --recursive
  END;
  RETURN iResult;
  EXCEPTION WHEN NO_DATA_FOUND THEN
    RETURN NULL;
  END;
END GoUpLevel;
/