Microsoft Access SQL：如何基于指定日期查询商品最近录入价格

你的两段SQL逻辑都存在错误，导致无法得到预期结果：

• 第一段SQL：WHERE子句中把日期类型的CSTDTE和整数类型的日期差结果直接比较，类型不匹配且逻辑完全错误，自然无返回结果。
• 第二段SQL：同样犯了日期与整数比较的逻辑错误，这个条件无法筛选出最近日期的记录，因此返回了所有匹配商品编号的行。

要查询指定商品编号下，价格录入日期与目标订单日期（'2023/04/03'）最接近的价格记录，可采用以下两种写法：

方法1：使用TOP 1排序筛选（适合SQL Server等支持TOP的数据库） #

SELECT TOP 1 CSTPN, CSTMTL, CSTDTE
FROM CSM400MFG_FCCSTHST
WHERE CSTPN = 'AARES02NS'
-- 先按日期差绝对值升序（最近的排最前），再按日期升序（差相同时取更早的记录）
ORDER BY ABS(DATEDIFF(d, '2023/04/03', CSTDTE)) ASC, CSTDTE ASC

方法2：使用窗口函数（通用型，支持SQL Server、MySQL 8+、PostgreSQL等） #

如果需要批量处理多个商品编号，或者更灵活控制排序规则，窗口函数是更好的选择：

SELECT CSTPN, CSTMTL, CSTDTE
FROM (
    SELECT 
        CSTPN, CSTMTL, CSTDTE,
        -- 按商品编号分组，每组内按日期差绝对值、日期排序，生成行号
        ROW_NUMBER() OVER (
            PARTITION BY CSTPN 
            ORDER BY ABS(DATEDIFF(d, '2023/04/03', CSTDTE)) ASC, CSTDTE ASC
        ) AS rn
    FROM CSM400MFG_FCCSTHST
    WHERE CSTPN = 'AARES02NS'
) AS temp
-- 取每组行号为1的记录（即最近日期的那条）
WHERE rn = 1

说明 #

• ABS(DATEDIFF(d, '2023/04/03', CSTDTE))计算价格录入日期与订单日期的天数差绝对值，确保不管是早于还是晚于订单日期，都能找到最近的。
• 第二个排序条件CSTDTE ASC是为了处理极端情况：如果有两条记录与订单日期的天数差相同（比如一个早3天，一个晚3天），会优先返回更早的那条，符合你期望返回2023/3/30的需求。如果需要优先返回晚于订单日期的，改成CSTDTE DESC即可。

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