在Access中是否可以创建递归查询？

Question

我有一个job表

Id
ParentID
jobName
jobStatus

根节点的ParentID为0。

在Access中是否可以创建一个查询来找到给定工作的根节点？ 该数据库是没有链接表的MDB格式，并且使用的Access版本是2003。工作可能有多层子孙。

- THEn

---

请在说“工作可以在几个孙子级别”时更加精确，或者粘贴一些虚拟数据。 - Anand Shah

Answer 1

在Access中，可以创建一个查询来查找给定作业的根源。不要忘记VBA函数的强大功能，您可以在VBA模块中创建一个递归函数，并将其结果用作查询中的输出字段。

示例：

Public Function JobRoot(Id As Long, ParentId As Long) As Long
   If ParentId = 0 Then
      JobRoot = Id
      Exit Function
   End If

   Dim Rst As New ADODB.Recordset
   Dim sql As String
   sql = "SELECT Id, ParentID FROM JobTable WHERE Id = " & ParentId & ";"
   Rst.Open sql, CurrentProject.Connection, adOpenKeyset, adLockReadOnly

   If Rst.Fields("ParentID") = 0 Then
      JobRoot = Rst.Fields("Id")
   Else
      JobRoot = JobRoot(Id, Rst.Fields("ParentID"))    ' Recursive.
   End If

   Rst.Close
   Set Rst = Nothing
End Function

您可以通过查询构建器调用这个递归函数，或者只需在查询字段中键入带有参数的函数名称即可。它将产生根节点。

（我知道这个问题已经一年了，但当每个人都说不可能变成可能时，我还是有必要回答的）。

- pointer

---

我赞赏你的发帖，但原始问题实际上将答案限制在查询范围内，而这实际上是不可能的，除非使用VBA。是否公平地将“在查询中”解释为“不依赖自定义VBA函数”是另一个问题。我认为即使不是原帖作者想要的纯SQL答案，将你的解决方案放在这里也是好的，因为有相同问题的其他人可能对此没有异议。 - David-W-Fenton

这样做非常低效，因为它意味着每次调用都要打开一个新的记录集。我建议至少将记录集保留在模块级变量中，或者将整个记录集加载到内存中的模块级Scripting.Dictionary中，这可能会允许更快的访问，就像这样。@David-W-Fenton - Zev Spitz

Answer 2

不，它不支持。自SServer 2005以后，SQL Server支持递归查询，但Access不支持。

如果您事先知道级别数量，可以编写查询，但它不是递归查询。

在SQL Server中，使用CTE（一种SQL扩展）来完成此操作：请参见http://blog.crowe.co.nz/archive/2007/09/06/Microsoft-SQL-Server-2005---CTE-Example-of-a-simple.aspx 然而，常规SQL不支持递归。

- Peter

---

@onedaywhen：「ISO/ANSI标准SQL」是否应该有一个后缀年份，以帮助区分它和「ISO/ANSI标准SQL-99」？ - Martin F

Answer 3

您不能递归查询。

您可以进行任意数量的左连接，但只能向上进行与连接数量相同的级别。

或者您可以使用Celko的“嵌套集模型”来检索所有父级。这将需要修改您的表结构，使插入和更新更加复杂。

- tpdi

---

无法进行递归查询。在Access 03中无法进行，但在许多其他查询语言中可以。 - Peter

是的，“你”这个使用Access的OP，无法进行递归查询。 - tpdi

尽管嵌套集合是由Celko推广的，但该方法归功于Michael J. Kamfonas（http://en.wikipedia.org/wiki/Joe_Celko）。 - onedaywhen

Answer 4

在Access中无法使用纯SQL完成此操作，但稍加VBA编程可以轻松解决。

请添加对Microsoft Scripting Runtime的引用（工具->引用...）。

假设ID是唯一的，并且不存在循环：例如A的父项是B，但B的父项是A。

Dim dict As Scripting.Dictionary

Function JobRoot(ID As Long) As Long
    If dict Is Nothing Then
        Set dict = New Scripting.Dictionary
        Dim rs As DAO.Recordset
        Set rs = CurrentDb.OpenRecordset("SELECT ID, ParentID FROM Job", dbOpenForwardOnly, dbReadOnly)
        Do Until rs.EOF
            dict(rs!ID) = rs!ParentID
            rs.MoveNext
        Loop
        Set rs = Nothing

        Dim key As Variant
        For Each key In dict.Keys
            Dim possibleRoot As Integer
            possibleRoot = dict(key)
            Do While dict(possibleRoot) <> 0
                possibleRoot = dict(possibleRoot)
            Loop
            dict(key) = possibleRoot
        Next
    End If
    JobRoot = dict(ID)
End Function

Sub Reset() 'This needs to be called to refresh the data
    Set dict = Nothing
End Sub

- Zev Spitz

Answer 5

好的，这是真正的内容。首先，你的查询面向的受众是什么？是表格吗？报告？函数/过程？

表格：需要更新？使用树视图控件，虽然有点繁琐，但会很好用。 报告：在打开事件中使用参数表单来设置“Boss Job”级别，然后在vba中处理递归并用数据按所需顺序填充记录集。将报表的记录集设置为这个填充的记录集并处理报表。 函数/过程：基本上与上述报告中描述的数据加载相同。通过代码处理必要的“树形遍历”并将结果集以所需顺序存储在记录集中，然后根据需要进行处理。

- Mark Pashalis

---

什么是Treeview控件？据我所知，在任何版本的Access中都没有原生的Treeview可用。 - David-W-Fenton

这可能是现有答案的一个很好的补充，但它需要改进。例如，删除“好的，这就是真正的交易。”；将选项分成项目符号或段落。扩展并澄清每个选项。 - Martin F

@David-W-Fenton，也许你发帖时不是这样，但现在已经有了。我使用的是Access 2010，并且其中包含了TreeView作为一个ActiveX控件。 - June7

Answer 6

我曾经遇到一个与树形结构有关的问题，当用户想要删除一个节点时，他希望删除该树下的所有节点。子节点，子节点的子节点等等。

这需要使用递归来解决……

因此，为了删除表中的数据以匹配树形视图节点的删除，使用一个递归函数来删除节点，并向下递归并删除所有子节点、孙子节点等。

以下是函数示例：

Public Sub RemoveChildKeys(MyKey)
' deletes passed key and removes all children and grandchildren ect etc of passed key recursively

   Dim TheDB As DAO.Database
   Dim TheTable As DAO.Recordset
   Dim MySql As String

   Set TheDB = CurrentDb

   MySql = "Select * from TblIndex WHERE [Parent]=" & MyKey & ";"
   Set TheTable = TheDB.OpenRecordset(MySql)

   While Not TheTable.EOF
     RemoveChildKeys (TheTable!Key)  ' <---- Calls itself
     TheTable.MoveNext
   Wend

   DoCmd.RunSQL "Delete * FROM TblIndex WHERE [Key]=" & MyKey  ' delete in table

End Sub

- Ratbyte Boss

Answer 7

Zev的贡献给了我很多灵感和学习，但需要对代码进行一些编辑。请注意我的表格名称为"tblTree"。

Dim dict As Scripting.Dictionary

Function TreeRoot(ID As Long) As Long
    If dict Is Nothing Then
        Set dict = New Scripting.Dictionary ' Requires Microsoft Scripting Runtime
        Dim rs As DAO.Recordset
        Set rs = CurrentDb.OpenRecordset("tblTree", dbOpenForwardOnly, dbReadOnly)
        Do Until rs.EOF
            dict.Add (rs!ID), (rs!ParentID)
            rs.MoveNext
        Loop
        Set rs = Nothing
    End If

    TreeRoot = ID

    Do While dict(TreeRoot) <> 0    ' Note: short version for dict.item(TreeRoot)
        TreeRoot = dict(TreeRoot)
    Loop
End Function

同样的上下文中还有一个有用的函数。如果孩子节点与任何嵌套层次中提供的ParentID匹配，则“ChildHasParent”返回true。

Function ChildHasParent(ID As Long, ParentID As Long) As Boolean
    If dict Is Nothing Then
        Set dict = New Scripting.Dictionary ' Requires Microsoft Scripting Runtime
        Dim rs As DAO.Recordset
        Set rs = CurrentDb.OpenRecordset("tblTree", dbOpenForwardOnly, dbReadOnly)
        Do Until rs.EOF
            dict.Add (rs!ID), (rs!ParentID)
            rs.MoveNext
        Loop
        Set rs = Nothing
    End If

    ChildHasParent = False

    Do While dict(ID) <> 0    ' Note: short version for dict.item(TreeRoot)
        ID = dict(ID)
        If ID = ParentID Then
            ChildHasParent = True
            Exit Do
        End If
    Loop
End Function

- Ben