如何查询聚合父子实体及下级的累计部件数量？

需求与现有问题 #

• 核心表结构：
  • tblEntity：自连接表，存储实体父子关联（字段：EntID、ParentEntID）
  • tblEntWdg：存储实体-部件分配关系及数量（字段：EntID、WdgID、Qty）
• 目标：统计每个实体及其所有下级子实体的部件累计数量
• 当前方案痛点：
  1. 基于qryEntLvl+UNION的层级查询仅支持最多5层，无法适配任意深度的实体结构
  2. 执行效率低下，尝试VBA递归但无实施思路
  3. 关联其他表时易触发内存不足错误，临时表方案效率仍不理想

VBA递归实现方案 #

1. 递归函数获取实体全量下级ID #

该函数传入实体ID，返回包含自身及所有下级子实体ID的集合：

Function GetAllChildEntIDs(parentID As Variant) As Collection
    Dim coll As New Collection
    Dim rs As DAO.Recordset
    Dim childID As Variant
    
    ' 加入当前实体ID
    coll.Add parentID
    
    ' 查询直接子实体
    Set rs = CurrentDb.OpenRecordset("SELECT EntID FROM tblEntity WHERE ParentEntID = " & parentID)
    
    Do While Not rs.EOF
        childID = rs!EntID
        ' 递归获取子实体的所有下级ID并合并到集合
        Dim subColl As Collection
        Set subColl = GetAllChildEntIDs(childID)
        For Each item In subColl
            coll.Add item
        Next
        rs.MoveNext
    Loop
    
    rs.Close
    Set GetAllChildEntIDs = coll
End Function

2. 生成累计统计结果 #

遍历所有顶级实体（无父实体的节点），调用递归函数获取全量关联ID，再聚合部件数量并存储到结果表：

Sub GenerateEntWdgTotal()
    Dim rsTopEnt As DAO.Recordset
    Dim entColl As Collection
    Dim entID As Variant
    Dim sql As String
    Dim idList As String
    
    ' 创建结果存储表（若已存在则重建）
    On Error Resume Next
    CurrentDb.Execute "DROP TABLE tblEntWdgTotal"
    On Error GoTo 0
    CurrentDb.Execute "CREATE TABLE tblEntWdgTotal (EntID Long, WdgID Long, TotalQty Long)"
    
    ' 遍历所有顶级实体
    Set rsTopEnt = CurrentDb.OpenRecordset("SELECT EntID FROM tblEntity WHERE ParentEntID Is Null OR ParentEntID = 0")
    
    Do While Not rsTopEnt.EOF
        entID = rsTopEnt!EntID
        ' 获取当前实体及其所有下级ID集合
        Set entColl = GetAllChildEntIDs(entID)
        
        ' 转换ID集合为逗号分隔字符串，用于SQL IN子句
        idList = ""
        For Each item In entColl
            idList = idList & "," & item
        Next
        idList = Mid(idList, 2) ' 移除开头多余的逗号
        
        ' 聚合部件数量并插入结果表
        sql = "INSERT INTO tblEntWdgTotal (EntID, WdgID, TotalQty) " & _
              "SELECT " & entID & " AS EntID, WdgID, Sum(Qty) AS TotalQty " & _
              "FROM tblEntWdg " & _
              "WHERE EntID IN (" & idList & ") " & _
              "GROUP BY WdgID"
        
        CurrentDb.Execute sql
        
        rsTopEnt.MoveNext
    Loop
    
    rsTopEnt.Close
    MsgBox "统计完成，结果已存入tblEntWdgTotal表"
End Sub

性能优化建议 #

• 索引优化：给tblEntity.ParentEntID、tblEntity.EntID、tblEntWdg.EntID、tblEntWdg.WdgID创建单独索引，大幅提升查询和聚合速度
• 缓存递归结果：添加全局字典存储已查询过的实体ID对应的下级集合，避免重复递归计算相同节点
• 批量处理：若顶级实体数量极大，可分批次获取并处理，减少单次内存占用
• 替代IN子句：当ID数量过多时，改用临时表存储ID集合后关联查询，避免SQL字符串过长导致的内存溢出

内容的提问来源于stack exchange，提问作者jason