您好, 欢迎来到 !    登录 | 注册 | | 设为首页 | 收藏本站

具有多个级别的MS-Access SQL自连接

具有多个级别的MS-Access SQL自连接

考虑以下功能集:

Function BuildQuerysql(lngsid As Long) As String
    Dim intlvl As Integer
    Dim strsel As String: strsel = selsql(intlvl)
    Dim strfrm As String: strfrm = "people as p0 "
    Dim strwhr As String: strwhr = "where p0.supervisor = " & lngsid

    While HasRecordsP(strsel & strfrm & strwhr)
        intlvl = intlvl + 1
        BuildQuerysql = BuildQuerysql & " union " & strsel & strfrm & strwhr
        strsel = selsql(intlvl)
        If intlvl > 1 Then
            strfrm = "(" & strfrm & ")" & frmsql(intlvl)
        Else
            strfrm = strfrm & frmsql(intlvl)
        End If
    Wend
    BuildQuerysql = Mid(BuildQuerysql, 8)
End Function

Function HasRecordsP(strsql As String) As Boolean
    Dim dbs As DAO.Database
    Set dbs = CurrentDb
    With dbs.OpenRecordset(strsql)
        HasRecordsP = Not .EOF
        .Close
    End With
    Set dbs = Nothing
End Function

Function selsql(intlvl As Integer) As String
    selsql = "select p" & intlvl & ".personid from "
End Function

Function frmsql(intlvl As Integer) As String
    frmsql = " inner join people as p" & intlvl & " on p" & intlvl - 1 & ".personid = p" & intlvl & ".supervisor "
End Function

在这里BuildQuerysql可以为函数提供与PersonID对应的,Supervisor并且函数将为适当的查询返回“递归” sql代码,以获得PersonID主管的所有下属的。

因此,可以评估该函数以构造保存的查询,例如,对于具有的主管PersonID = 5,创建名为的查询Subordinates

Sub test()
    CurrentDb.CreateQueryDef "Subordinates", BuildQuerysql(5)
End Sub

或者根据您的应用程序的要求,对sql进行评估以打开结果的RecordSet。

请注意,该函数构造一个UNION查询,嵌套的每个级别都与前一个查询结合在一起。

Access 2022/1/1 18:52:36 有219人围观

撰写回答


你尚未登录,登录后可以

和开发者交流问题的细节

关注并接收问题和回答的更新提醒

参与内容的编辑和改进,让解决方法与时俱进

请先登录

推荐问题


联系我
置顶