MS Access查询中如何比较当前行FROM与上一行TO的值？

实现方案 #

在Access低版本中没有原生窗口函数，要实现当前记录与上一条记录的字段对比，需通过子查询定位同钻孔的上一条记录，再完成区间重叠判断。核心是获取当前记录所属钻孔中，排序在前的那条记录的TO值，与当前记录的FROM做比较。

修改后的完整查询 #

SELECT 
    T_Alteration.[HOLE ID],
    T_Alteration.FROM,
    T_Alteration.TO,
    T_Alteration.Length,
    T_Alteration.[Alt Code],
    T_Alteration.Remarks,
    -- 先判断重复记录，再处理区间重叠逻辑
    IIf(
        Not IsNull(Alteration.HoleID),
        "SKIPPED -Already in Record!",
        IIf(
            -- 处理第一条记录（无前置记录）的情况
            IsNull((SELECT TOP 1 TA.TO 
                    FROM T_Alteration AS TA 
                    WHERE TA.[HOLE ID] = T_Alteration.[HOLE ID] 
                      AND TA.FROM < T_Alteration.FROM 
                    ORDER BY TA.FROM DESC)),
            "OK",
            -- 对比当前FROM与上一条TO
            IIf(
                (SELECT TOP 1 TA.TO 
                 FROM T_Alteration AS TA 
                 WHERE TA.[HOLE ID] = T_Alteration.[HOLE ID] 
                   AND TA.FROM < T_Alteration.FROM 
                 ORDER BY TA.FROM DESC) < T_Alteration.FROM,
                "OK",
                "ERROR"
            )
        )
    ) AS Status
FROM 
    T_Alteration 
LEFT JOIN 
    Alteration ON (T_Alteration.TO = Alteration.GEOLTO) 
              AND (T_Alteration.FROM = Alteration.GEOLFROM) 
              AND (T_Alteration.[HOLE ID] = Alteration.HoleID)
ORDER BY 
    T_Alteration.[HOLE ID], T_Alteration.FROM;

关键逻辑说明 #

1. 子查询定位上一条记录：

   SELECT TOP 1 TA.TO 
   FROM T_Alteration AS TA 
   WHERE TA.[HOLE ID] = T_Alteration.[HOLE ID] 
     AND TA.FROM < T_Alteration.FROM 
   ORDER BY TA.FROM DESC
   

   该子查询会在同HOLE ID的记录中，筛选出FROM值小于当前记录的所有条目，再按FROM降序取第一条（即排序最靠近当前记录的上一条），返回其TO值。

2. 嵌套IIf的判断逻辑：

   • 优先判断原查询中的重复记录：如果Alteration.HoleID不为空，标记为SKIPPED -Already in Record!
   • 若为钻孔的第一条记录（子查询返回Null），直接标记OK
   • 最后对比当前FROM与上一条TO：如果当前FROM >= 上一条TO则标记OK，否则标记ERROR

3. 排序保证准确性：
   末尾的ORDER BY T_Alteration.[HOLE ID], T_Alteration.FROM确保同钻孔的记录按FROM升序排列，保证子查询能正确匹配到"上一条"记录。

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