使用Access SQL查询指定人员/亲属的所有关联对象

表结构回顾 #

首先明确三张表的结构与数据：

tbl_Person #

tbl_Relative #

tbl_Relation #

作为关联桥梁，字段包括RelationID、PersonID、RelativeID、Relation，用于记录Person与Relative之间的关联关系。

需求目标 #

输入任意一个Person或Relative的ID，查询出所有直接或间接关联的对象（例如查询Derby(1004)时，需返回Zack(2001)和Abraham(1001)）。

实现步骤 #

1. 合并Person与Relative为统一数据集 #

首先将两张表合并，生成包含所有人员/亲属的统一列表，方便后续关联查询：

SELECT PersonID AS ID, Name, Age, 'Person' AS category 
FROM tbl_Person
UNION
SELECT RelativeID AS ID, Name, Age, 'Relative' AS category 
FROM tbl_Relative

可以将上述查询创建为视图，简化后续递归查询：

CREATE VIEW vw_AllIndividuals AS
SELECT PersonID AS ID, Name, Age, 'Person' AS category 
FROM tbl_Person
UNION
SELECT RelativeID AS ID, Name, Age, 'Relative' AS category 
FROM tbl_Relative;

2. 递归查询所有关联对象 #

利用Access的递归CTE（WITH RECURSIVE）实现直接+间接关联的查询，替换[TargetID]为你要查询的ID即可：

WITH RECURSIVE RelatedNodes AS (
    -- 起始节点：选择目标对象
    SELECT ID, Name, Age, category
    FROM vw_AllIndividuals
    WHERE ID = [TargetID]

    UNION ALL

    -- 递归遍历关联节点
    SELECT ai.ID, ai.Name, ai.Age, ai.category
    FROM RelatedNodes rn
    JOIN tbl_Relation r 
        ON (rn.category = 'Person' AND r.PersonID = rn.ID) 
        OR (rn.category = 'Relative' AND r.RelativeID = rn.ID)
    JOIN vw_AllIndividuals ai
        ON (r.RelativeID = ai.ID AND rn.category = 'Person')
        OR (r.PersonID = ai.ID AND rn.category = 'Relative')
    WHERE ai.ID NOT IN (SELECT ID FROM RelatedNodes) -- 避免循环查询
)
-- 排除目标对象自身，返回所有关联对象
SELECT * FROM RelatedNodes
WHERE ID <> [TargetID];

示例验证 #

• 当[TargetID] = 2002（Yan）时，查询结果为Bill(1002)
• 当[TargetID] = 1004（Derby）时，查询结果为Zack(2001)和Abraham(1001)

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