Access员工座位历史追踪查询构建：多异动场景解决方案需求

Access 实现方案 #

方法1：自连接子查询（推荐，性能更优） #

通过自连接子查询定位每条记录的下一次异动日期，自动处理重复回座的情况（每次回座会生成独立的起止区间），无需新增冗余记录。

SELECT 
    sh.Employee,
    sh.Seat,
    sh.RecordDate AS StartDate,
    Nz(
        (SELECT MIN(sh_next.RecordDate) 
         FROM SeatsHistory sh_next 
         WHERE sh_next.Employee = sh.Employee 
           AND sh_next.Seat = sh.Seat 
           AND sh_next.RecordDate > sh.RecordDate),
        NULL
    ) AS EndDate
FROM SeatsHistory sh
WHERE NOT EXISTS (
    SELECT 1 
    FROM SeatsHistory sh_prev 
    WHERE sh_prev.Employee = sh.Employee 
      AND sh_prev.Seat = sh.Seat 
      AND sh_prev.RecordDate = (
          SELECT MAX(RecordDate) 
          FROM SeatsHistory 
          WHERE Employee = sh.Employee 
            AND Seat = sh.Seat 
            AND RecordDate < sh.RecordDate
      )
)
ORDER BY sh.Employee, sh.RecordDate;

• 内层子查询MIN(sh_next.RecordDate)获取同员工同座位的下一条异动日期，作为当前记录的结束日期；Nz函数将无后续记录的情况转为空值，表示当前有效。
• NOT EXISTS子句过滤连续的同座位重复记录，确保每个起止区间仅保留起始记录。

方法2：DLookup实现（仅适合小数据集） #

若必须使用DLookup，可采用以下写法，但大数据量下性能较差：

SELECT 
    Employee,
    Seat,
    RecordDate AS StartDate,
    DLookup(
        "MIN(RecordDate)", 
        "SeatsHistory", 
        "Employee = '" & [Employee] & "' AND Seat = '" & [Seat] & "' AND RecordDate > #" & [RecordDate] & "#"
    ) AS EndDate
FROM SeatsHistory
WHERE DLookup(
        "MAX(RecordDate)", 
        "SeatsHistory", 
        "Employee = '" & [Employee] & "' AND Seat = '" & [Seat] & "' AND RecordDate < #" & [RecordDate] & "#"
    ) IS NULL
ORDER BY Employee, RecordDate;

注意：字段类型为数字时需去掉引号，日期格式需符合Access要求。

主流RDBMS实现差异 #

1. SQL Server #

支持LAG()/LEAD()窗口函数，写法简洁高效：

WITH SeatChanges AS (
    SELECT 
        Employee,
        Seat,
        RecordDate,
        LAG(Seat) OVER (PARTITION BY Employee ORDER BY RecordDate) AS PrevSeat
    FROM SeatsHistory
),
ValidChanges AS (
    SELECT 
        Employee,
        Seat,
        RecordDate AS StartDate,
        LEAD(RecordDate) OVER (PARTITION BY Employee, Seat ORDER BY RecordDate) AS EndDate
    FROM SeatChanges
    WHERE PrevSeat != Seat OR PrevSeat IS NULL
)
SELECT 
    Employee,
    Seat,
    StartDate,
    CASE WHEN EndDate IS NULL THEN NULL ELSE EndDate END AS EndDate
FROM ValidChanges
ORDER BY Employee, StartDate;

• LAG()判断是否为连续同座位记录，LEAD()直接获取下一次异动日期，性能远超自连接。

2. MySQL 8.0+ / PostgreSQL #

同样支持窗口函数，写法类似SQL Server：

WITH SeatChanges AS (
    SELECT 
        Employee,
        Seat,
        RecordDate,
        LAG(Seat) OVER (PARTITION BY Employee ORDER BY RecordDate) AS PrevSeat
    FROM SeatsHistory
)
SELECT 
    Employee,
    Seat,
    RecordDate AS StartDate,
    LEAD(RecordDate) OVER (PARTITION BY Employee, Seat ORDER BY RecordDate) AS EndDate
FROM SeatChanges
WHERE PrevSeat != Seat OR PrevSeat IS NULL
ORDER BY Employee, StartDate;

3. Oracle #

支持窗口函数，可结合NULLIF处理空值：

WITH SeatChanges AS (
    SELECT 
        Employee,
        Seat,
        RecordDate,
        LAG(Seat) OVER (PARTITION BY Employee ORDER BY RecordDate) AS PrevSeat
    FROM SeatsHistory
)
SELECT 
    Employee,
    Seat,
    RecordDate AS StartDate,
    LEAD(RecordDate) OVER (PARTITION BY Employee, Seat ORDER BY RecordDate) AS EndDate
FROM SeatChanges
WHERE PrevSeat != Seat OR PrevSeat IS NULL
ORDER BY Employee, StartDate;

核心说明 #

• 无冗余记录：所有方案均基于现有SeatsHistory表计算，无需额外存储数据。
• 重复回座处理：每次回座生成的新记录会被识别为独立的起止区间，符合业务异动逻辑。
• 性能对比：支持窗口函数的RDBMS性能最优，Access自连接优于DLookup。

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