使用关联子查询计算客户最近两次订单日期差值的SQL问题

嘿，我来帮你搞定这个需求～先看看你现有SQL语句里的几个小问题：

• 分组时带上了b1.besteldatum，这会导致每个订单行都被单独分组，没法按客户聚合出统一结果
• 子查询里的MAX(b1.Besteldatum)没关联到当前客户，会返回全表的最大订单日期，不是单个客户自己的最近订单
• 用NOT IN排除最近日期的逻辑不够严谨，如果某个客户有多个相同的最新订单，结果会出错

下面给你两种用关联子查询实现的正确写法，帮你精准计算每个客户最近两次订单的日期差：

写法一：直接关联子查询取两次日期 #

SELECT 
    b.klantnr,
    DATEDIFF(DAY, 
             -- 子查询1：获取当前客户的上一次订单日期（排除最近日期后的最大日期）
             (SELECT MAX(b2.Besteldatum)
              FROM bestelling b2
              WHERE b2.klantnr = b.klantnr
                AND b2.Besteldatum < (SELECT MAX(b3.Besteldatum)
                                     FROM bestelling b3
                                     WHERE b3.klantnr = b.klantnr)),
             -- 子查询2：获取当前客户的最近订单日期
             (SELECT MAX(b3.Besteldatum)
              FROM bestelling b3
              WHERE b3.klantnr = b.klantnr)
    ) AS verschil
FROM bestelling b
GROUP BY b.klantnr
-- 过滤掉只有1次订单的客户（没有上一次订单，差值无意义）
HAVING COUNT(DISTINCT b.Besteldatum) >= 2;

代码说明： #

• 外层按klantnr（客户编号）分组，确保每个客户只返回一行结果
• 两个关联子查询都通过b2.klantnr = b.klantnr绑定到当前客户，保证取到的是该客户自己的订单日期
• HAVING子句过滤掉订单数不足两次的客户，避免出现NULL差值

写法二：子查询排名后取最近两次订单 #

SELECT 
    klantnr,
    DATEDIFF(DAY, MIN(Besteldatum), MAX(Besteldatum)) AS verschil
FROM (
    SELECT 
        b1.klantnr,
        b1.Besteldatum,
        -- 子查询：给当前客户的订单按日期倒序"排名"，最新订单为0，上一次为1
        (SELECT COUNT(*)
         FROM bestelling b2
         WHERE b2.klantnr = b1.klantnr
           AND b2.Besteldatum > b1.Besteldatum) AS order_rank
    FROM bestelling b1
) AS ranked_orders
-- 只保留最近的两次订单
WHERE order_rank IN (0, 1)
GROUP BY klantnr
HAVING COUNT(*) = 2;

代码说明： #

• 内层子查询通过统计"比当前订单日期晚的订单数量"，实现了给每个客户的订单倒序排名
• 外层筛选出排名前两位的订单，再按客户分组计算日期差，逻辑更直观

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