SQLite窗口函数应用：查询指定日期下单供应商的上一次订单日期

原始采购数据 #

假设我们的采购表名为purchases，原始数据如下：

purchasingid | date       | supplierid
1            | 2014-01-01 | 12
2            | 2014-01-01 | 13
3            | 2013-12-06 | 12
4            | 2013-12-05 | 11
5            | 2014-01-01 | 17
6            | 2013-12-05 | 12

需求回顾 #

我们需要达成的目标：

• 仅保留2014-01-01有下单记录的供应商
• 获取这些供应商早于2014-01-01的最近订单日期
• 若该供应商在2014-01-01之前没有任何订单，last_time_buy_date字段留空
• 直接排除2014-01-01没有下单的供应商（比如supplierid=11）

高效SQL实现（窗口函数版） #

推荐用窗口函数的方式，逻辑清晰且性能更优：

WITH supplier_order_sequence AS (
    SELECT 
        supplierid,
        date,
        -- 按供应商分组、日期排序，取上一条订单的日期
        LAG(date) OVER (PARTITION BY supplierid ORDER BY date) AS last_time_buy_date
    FROM purchases
)
SELECT 
    supplierid,
    date,
    last_time_buy_date
FROM supplier_order_sequence
WHERE date = '2014-01-01'
ORDER BY supplierid;

执行结果 #

运行后会得到你期望的输出：

supplierid | date       | last_time_buy_date
12         | 2014-01-01 | 2013-12-06
13         | 2014-01-01 | 
17         | 2014-01-01 | 

代码逻辑解释 #

1. CTE supplier_order_sequence：先对每个供应商的订单按日期排序，用LAG(date)函数抓取同一供应商的上一笔订单日期。PARTITION BY supplierid确保我们只在同一供应商的订单组内计算，ORDER BY date保证按时间顺序获取上一条记录。
2. 主查询：从CTE中筛选出日期为2014-01-01的记录，精准保留当天有下单的供应商，同时带出他们的上一次订单日期（无历史订单则返回NULL，显示为空）。

兼容老版本数据库的替代方案（关联子查询） #

如果你的数据库不支持窗口函数，也可以用关联子查询实现：

SELECT 
    p1.supplierid,
    p1.date,
    (SELECT MAX(p2.date) 
     FROM purchases p2 
     WHERE p2.supplierid = p1.supplierid 
       AND p2.date < p1.date) AS last_time_buy_date
FROM purchases p1
WHERE p1.date = '2014-01-01'
ORDER BY p1.supplierid;

这个子查询的逻辑是：对每个2014-01-01的订单，找到同一供应商早于该日期的最大订单日期（也就是最近的上一次订单），没有符合条件的记录则返回NULL。

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