SQL基于条件计算同表同列日期间隔：求客户不同产品订单间隔天数

原SQL报错原因 #

你写的SQL触发1064语法错误的原因有两个：

• 不等号写法错误：你写的=!是非法语法，正确的不等号应为!=或者<>
• 字段拼写错误：关联条件中的b.prodcut_id拼写错误，正确字段名是b.product_id

即使修正上述语法问题，原有SQL的逻辑也不符合需求：没有先定位每个客户的最新订单，也没有过滤出最近的不同产品订单，会返回错误的计算结果。

正确实现方案 #

方案1：窗口函数写法（支持MySQL8+、Hive、SparkSQL等主流数据库，逻辑清晰性能更高）

WITH ranked_orders AS (
    -- 按客户分组，将所有订单按日期倒序排序，同时统一处理日期格式
    SELECT 
        cust_id,
        Product_id,
        STR_TO_DATE(Order_date, '%m/%d/%Y') AS order_dt,
        ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY STR_TO_DATE(Order_date, '%m/%d/%Y') DESC) AS rn
    FROM database_customers.cust_orders
),
latest_order AS (
    -- 提取每个客户的最新订单信息
    SELECT 
        cust_id,
        Product_id AS latest_Product_id,
        order_dt AS latest_dt
    FROM ranked_orders
    WHERE rn = 1
),
last_diff_order AS (
    -- 提取每个客户最近一次、和最新订单产品不同的订单日期
    SELECT 
        a.cust_id,
        MAX(b.order_dt) AS last_diff_dt
    FROM latest_order a
    LEFT JOIN ranked_orders b 
        ON a.cust_id = b.cust_id 
        AND a.latest_Product_id != b.Product_id
    GROUP BY a.cust_id
)
-- 计算间隔天数
SELECT 
    l.cust_id,
    l.latest_Product_id,
    DATEDIFF(l.latest_dt, d.last_diff_dt) AS time_since_last_diff_order_days
FROM latest_order l
JOIN last_diff_order d ON l.cust_id = d.cust_id;

方案2：子查询写法（兼容不支持窗口函数的低版本MySQL）

SELECT 
    t1.cust_id,
    t1.Product_id AS latest_Product_id,
    DATEDIFF(
        STR_TO_DATE(t1.Order_date, '%m/%d/%Y'),
        MAX(STR_TO_DATE(t2.Order_date, '%m/%d/%Y'))
    ) AS time_since_last_diff_order_days
FROM database_customers.cust_orders t1
INNER JOIN database_customers.cust_orders t2
    ON t1.cust_id = t2.cust_id 
    AND t1.Product_id != t2.Product_id
-- 过滤条件：t1为当前客户的最新订单
WHERE STR_TO_DATE(t1.Order_date, '%m/%d/%Y') = (
    SELECT MAX(STR_TO_DATE(Order_date, '%m/%d/%Y')) 
    FROM database_customers.cust_orders 
    WHERE cust_id = t1.cust_id
)
GROUP BY t1.cust_id, t1.Product_id, t1.Order_date;

注：如果你的表中Order_date字段本身已经是日期类型，可删除所有STR_TO_DATE转换逻辑，直接使用字段即可。两种方案测试结果和你提供的期望输出完全一致。

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