ms-access - recursividad - mysql recursive query tree

¿Es posible crear una consulta recursiva en Access?

Question

Tengo una mesa de job

Id
ParentID
jobName
jobStatus

El ParentID raíz es 0.

¿Es posible en Access crear una consulta para encontrar una raíz para un job determinado? La base de datos es MDB sin tablas vinculadas. La versión de Access es de 2003. Un job puede ser de varios niveles, nietos profundos.

Answer 1

Es posible en Access crear una consulta para encontrar la raíz de su trabajo dado. No te olvides del poder de las funciones VBA. Puede crear una función recursiva en un módulo VBA y usar su resultado como un campo de salida en su consulta.

Ejemplo:

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

Puede llamar a esta función recursiva desde su consulta utilizando el generador de consultas o simplemente escribiendo el nombre de la función con argumentos en un campo de consulta.

Cederá la raíz.

(Reconozco que el OP tiene ya un año, pero me veo obligado a responder cuando todos dicen que lo que es imposible es posible).

Answer 2

Esto no se puede hacer usando SQL puro en Access, pero un poco de VBA hace mucho.

Agregue una referencia a Microsoft Scripting Runtime ( Herramientas -> Referencias ... ).

Esto supone que la ID es única y que no hay ciclos: por ejemplo, el padre de A es B, pero el padre de B es 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

Answer 3

La contribución de Zev me dio mucha inspiración y aprendizaje. Sin embargo, necesitaba hacer algunas ediciones al código. Tenga en cuenta que mi tabla se llama "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

Y hay otra función útil en el mismo contexto. "ChildHasParent" devuelve true, si el niño coincide con el ParentID suministrado en cualquier nivel de anidación.

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

Answer 4

No puedes consultar recursivamente.

Puede hacer un número arbitrario de combinaciones izquierdas, pero solo podrá subir tantos niveles como desee.

O puede usar el "Modelo de conjunto anidado" de Celko para recuperar todos los padres. Esto requerirá la modificación de la estructura de la tabla, de manera que las inserciones y actualizaciones sean más complicadas.

Answer 5

No, no lo es. Las consultas recursivas son compatibles con SQL Server después de SServer 2005, pero no con Access.

Si conoces el número de niveles de antemano, podrías escribir una consulta, pero no sería recursiva.

En SQL Server, CTE (una extensión SQL) se usa para eso: ver http://blog.crowe.co.nz/archive/2007/09/06/Microsoft-SQL-Server-2005---CTE-Example-of-a-simple.aspx

Sin embargo, el SQL regular no tiene soporte de Recursividad.

Answer 6

OK, así que aquí está el trato REAL. Primero, ¿cuál es el público objetivo para su consulta ... un formulario? ¿informe? función / proc?

Formulario: ¿Necesita actualizaciones? use el control de vista en árbol mientras torpe funcionará bien. Informe: en el evento abierto use un formulario de parámetros para establecer el nivel de "Trabajo de jefe", luego maneje la recursión en vba y complete un juego de registros con los datos en el orden deseado . establezca el conjunto de registros de informes en este conjunto de registros completo y procese el informe. Función / Procedimiento: funciona prácticamente igual que la carga de datos descrita en el informe anterior. A través del código, maneje el "árbol que camina" necesario y almacene el conjunto de resultados en el orden deseado en un juego de registros y procese según sea necesario.