Access SQL无LEAD函数时，如何计算不同行的日期差值？

嘿，我懂你现在的困扰——Access确实没有Oracle里LEAD()那种便捷的窗口函数来直接抓取下一行的数据，但咱们完全可以用其他方式实现跨行列的日期差值计算，下面给你两种实用的方案：

方案一：用行号自连接实现 #

首先我们需要给目标记录按时间顺序分配行号，这里可以用DCount()函数来生成行号，再通过自连接关联当前行和下一行的记录：

1. 先创建带行号的子查询：

SELECT 
    FACTRY.*,
    DCount("*", "FACTRY", "job_number='30' AND finish_datetime < " & Format([finish_datetime], "\#mm\/dd\/yyyy hh\:nn\:ss\#")) + 1 AS row_num
FROM FACTRY
WHERE FACTRY.job_number='30'
ORDER BY finish_datetime;

这个查询会给job_number='30'的每条记录，按finish_datetime从小到大分配一个唯一的行号row_num。

2. 基于这个子查询做自连接，计算跨行日期差：

SELECT 
    t1.start_datetime,
    t1.finish_datetime,
    DateDiff("n", t1.finish_datetime, t2.start_datetime) AS cross_row_diff
FROM 
    (
        SELECT 
            FACTRY.*,
            DCount("*", "FACTRY", "job_number='30' AND finish_datetime < " & Format([finish_datetime], "\#mm\/dd\/yyyy hh\:nn\:ss\#")) + 1 AS row_num
        FROM FACTRY
        WHERE FACTRY.job_number='30'
    ) AS t1
LEFT JOIN 
    (
        SELECT 
            FACTRY.*,
            DCount("*", "FACTRY", "job_number='30' AND finish_datetime < " & Format([finish_datetime], "\#mm\/dd\/yyyy hh\:nn\:ss\#")) + 1 AS row_num
        FROM FACTRY
        WHERE FACTRY.job_number='30'
    ) AS t2 ON t1.row_num = t2.row_num - 1
ORDER BY t1.finish_datetime;

这里通过t1.row_num = t2.row_num - 1关联，t2的start_datetime就是t1下一行的开始时间，用DateDiff("n", ...)就能算出两行之间的分钟差了。

方案二：利用主键关联（如果表有唯一主键） #

如果你的FACTRY表有类似id的唯一自增主键，而且主键的顺序和时间顺序一致，那可以用更高效的子查询来获取下一行记录：

SELECT 
    t1.start_datetime,
    t1.finish_datetime,
    DateDiff("n", t1.finish_datetime, t2.start_datetime) AS cross_row_diff
FROM FACTRY t1
LEFT JOIN FACTRY t2 ON t2.job_number = t1.job_number 
    AND t2.id = (
        SELECT Min(id) 
        FROM FACTRY 
        WHERE job_number = t1.job_number AND id > t1.id
    )
WHERE t1.job_number='30'
ORDER BY t1.finish_datetime;

这个方法通过子查询找到当前记录之后的最小主键对应的记录，直接关联计算差值，性能比行号方法更好。

小提示 #

• Access里日期常量需要用#包裹，所以用Format()函数把日期转成#mm/dd/yyyy hh:nn:ss#格式，避免日期格式报错。
• 如果最后一行没有下一行数据，cross_row_diff会显示为Null，你可以用Nz()函数把它替换成0或者其他默认值，比如Nz(DateDiff("n", t1.finish_datetime, t2.start_datetime), 0)。

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