如何在MS Access 2019查询中获取父节点的所有子/后代节点？

Got it, I totally get the frustration—Access SQL doesn't support the recursive CTEs that work so smoothly in SQL Server, which is why those examples you found won't run directly. Since you're on Access 2019 and want a query-based solution instead of full VBA traversal, here are two practical approaches depending on your data's hierarchy depth:

方案1：多层嵌套子查询（适合层级较少/固定的场景） #

If you know your hierarchy only goes a few levels deep (e.g., 3 levels max), you can chain UNION ALL with self-joins to pull all descendants. For example, to get all children, grandchildren, and great-grandchildren of Id=3:

-- 直接子节点
SELECT Id, ParentID
FROM tbContentList
WHERE ParentID = 3

UNION ALL

-- 孙节点
SELECT c2.Id, c2.ParentID
FROM tbContentList c2
INNER JOIN tbContentList c1 ON c2.ParentID = c1.Id
WHERE c1.ParentID = 3

UNION ALL

-- 曾孙节点
SELECT c3.Id, c3.ParentID
FROM tbContentList c3
INNER JOIN tbContentList c2 ON c3.ParentID = c2.Id
INNER JOIN tbContentList c1 ON c2.ParentID = c1.Id
WHERE c1.ParentID = 3;

Pros: 无需VBA，直接在Access SQL中运行。
Cons: 每多一层层级，就需要手动添加新的UNION ALL块，层级多了会很繁琐。

方案2：VBA自定义函数+查询（支持任意层级递归） #

对于层级未知或较深的场景，可以创建一个递归VBA函数收集所有后代ID，再将其用于查询中。

步骤1：创建VBA函数 #

打开VBA编辑器（快捷键Alt+F11），插入新模块，粘贴以下代码：

Function GetAllDescendants(parentId As Long) As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim descendantIds As String
    
    Set db = CurrentDb()
    ' 获取当前父节点的直接子节点
    Set rs = db.OpenRecordset("SELECT Id FROM tbContentList WHERE ParentID = " & parentId)
    
    Do While Not rs.EOF
        ' 记录子节点ID
        descendantIds = descendantIds & "," & rs!Id
        ' 递归调用，获取子节点的所有后代
        descendantIds = descendantIds & GetAllDescendants(rs!Id)
        rs.MoveNext
    Loop
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    
    ' 移除开头多余的逗号
    If Len(descendantIds) > 0 Then
        GetAllDescendants = Mid(descendantIds, 2)
    Else
        GetAllDescendants = ""
    End If
End Function

步骤2：在SQL查询中使用该函数 #

现在可以编写查询，获取Id=3的所有后代（包括直接子节点）：

SELECT Id, ParentID
FROM tbContentList
WHERE Id IN (SELECT Val(SplitValue) FROM Split(GetAllDescendants(3), ","))
OR ParentID = 3;

如果只需要间接后代（排除直接子节点），去掉OR ParentID = 3即可。

Pros: 自动适配任意层级的结构，无需手动修改查询。
Cons: 需要少量VBA代码，但代码和查询绑定，无需单独运行VBA流程。

小提示 #

• 确保Id和ParentID字段数据类型一致（示例使用Long，如果是文本字段请调整函数代码）。
• 对于超大规模数据集，递归VBA函数可能有轻微性能损耗，但仍比写无数嵌套UNION高效得多。

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