我有下一张桌子。 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。

解决方案

回答

我认为我们需要为此使用CURSOR，此链接可能会有所帮助

回答

我认为不使用递归就无法实现这一点。我们可以使用mySQL通过一个存储过程完成此操作，但是默认情况下，存储过程中不允许进行递归。本文包含有关如何启用递归的信息。我不确定这会对性能产生多大的影响，而不是多重查询方法。 mySQL可能会对存储过程进行了一些优化，但否则我希望性能会相似。

回答

不知道我们是否有一个Users表，所以我通过存储在Group_Member表中的User_ID获取列表。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。

SELECT GroupUsers.User_ID,
        (
        SELECT 
            STUFF((SELECT ',' + 
            Cast(Group_ID As Varchar(10))
            FROM Group_Member Member (nolock) 
            WHERE Member.User_ID=GroupUsers.User_ID
        FOR XML PATH('')),1,1,'') 
        ) As Groups
FROM (SELECT User_ID FROM Group_Member GROUP BY User_ID) GroupUsers

返回：

User_ID    Groups
3          1
4          1
5          1
6          2,4
7          2
9          4
10         1
12         5

根据表中的数据，这似乎是正确的。但与期望值列表不匹配(例如，用户9在表数据中仅位于一组中，但在结果中显示为属于两个)

编辑：当当。只是注意到我们正在使用MySQL。我的解决方案是针对SQL Server。对不起。

-凯文·费尔柴尔德(Kevin Fairchild)

回答

在SQL标准中没有办法做到这一点，但是通常可以找到特定于供应商的扩展，例如，Oracle中的" CONNECT BY"。

更新：正如注释所指出的那样，它是在SQL 99中添加的。

回答

我想到两件事：

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个级别的视图来摆脱它。

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;
/

回答

Joe Cleko的书籍" SQL for Smarties"和" SQL for Smarties中的树和层次结构"描述了通过使用嵌套集完全避免递归的方法。这使更新变得复杂，但是使其他查询(通常需要递归)相对简单。乔在1996年写的这篇文章中有一些示例。

回答

已经提出了类似的问题。

这是我的答案(有点编辑)：

我不确定我是否正确理解了问题，但这可以解决我在SQL中对树的看法。

链接后描述了在数据库中存储树的方法(在这种情况下为PostgreSQL)，但是该方法很明确，因此可以轻松地用于任何数据库。

使用这种方法，我们可以使用大约N个简单的SELECTs查询轻松地更新所有依赖于修改后的节点K的节点，其中N是K到根节点的距离。

祝你好运！

回答

我不记得是在哪个SO问题下找到链接的，但sitepoint.com(第二页)上的这篇文章展示了另一种在表中存储层次树的方法，该方法可以轻松地找到所有子节点或者指向其的路径顶部，类似的东西。带有示例代码的很好的解释。

PS。对StackOverflow有点陌生，以上是否可以作为答案，或者它真的应该是对该问题的评论，因为它只是一个指向其他解决方案的指针(不能完全回答问题本身)？