MS Access多记录Inner Join更新问题：按规则筛选关联记录失效

问题背景 #

已找到SQL Server下的解决方案，但未找到MS Access的对应实现。现有两张表：

表NE #

IP           | Status | Peer
-----------------------------
10.10.10.1   | 0      | null
10.20.1.5    | 0      | null

表Peer #

IP           | Peer             | Status | Flap_Count
------------------------------------------------------
10.10.10.1   | 10.100.2.3       | 0      | 0
10.10.10.1   | 10.200.1.1       | 1      | 5
10.10.10.1   | 10.50.100.7      | 10     | 9
10.10.10.1   | 10.1.1.1         | 10     | 7 
10.20.1.5    | 10.20.20.7       | 10     | 5
10.20.1.5    | 10.2.2.10        | 5      | 2

需求 #

按照取Status最大且Flap_Count最大的记录规则，将Peer表的对应Status和Peer字段更新到NE表中，期望结果如下：

IP           | Status | Peer
-----------------------------
10.10.10.1   | 10     | 10.50.100.7
10.20.1.5    | 10     | 10.20.20.7

现有问题 #

编写的关联更新查询有时有效有时失效：

Update NE
Inner join (Select * from Peer Order by Status Desc, Flap_Count Desc) Q
On Q.IP = NE.IP
Set NE.Status = Q.Status, NE.Peer = Q.Peer
Where NE.Status = 0

该查询提示某IP有多条记录受影响，无法确定最终保留哪条，且修改排序方式后结果无变化。

正确的MS Access实现方法 #

Access的更新查询中，直接对排序后的子查询做JOIN无法保证只选取每组的第一条记录，必须先通过嵌套查询锁定每个IP需要的目标记录：

最终更新查询 #

UPDATE NE
INNER JOIN (
    SELECT p.IP, p.Peer, p.Status
    FROM Peer p
    INNER JOIN (
        SELECT p1.IP, p1.Status, MAX(p1.Flap_Count) AS MaxFlap
        FROM Peer p1
        INNER JOIN (
            SELECT IP, MAX(Status) AS MaxStatus
            FROM Peer
            GROUP BY IP
        ) s ON p1.IP = s.IP AND p1.Status = s.MaxStatus
        GROUP BY p1.IP, p1.Status
    ) f ON p.IP = f.IP AND p.Status = f.Status AND p.Flap_Count = f.MaxFlap
) target ON NE.IP = target.IP
SET NE.Status = target.Status, NE.Peer = target.Peer
WHERE NE.Status = 0

逻辑说明 #

1. 最内层查询：先按IP分组，获取每个IP的最大Status值
2. 中间层查询：基于最大Status值，再次按IP和Status分组，获取该Status下的最大Flap_Count值
3. 外层查询：关联Peer表，锁定同时满足最大Status和最大Flap_Count的记录，确保每个IP仅返回一条符合规则的结果
4. 最终将这些目标记录与NE表关联，完成更新操作

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