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.