SQL如何按指定列计算同列DATE差值 筛选停留超5天货件

表结构与需求说明 #

待处理的货件到离表字段规则：

• ID：货件唯一标识
• ARRIVAL：操作类型标记，取值IN为入库、OUT为出库
• DATE：操作对应的日期值

需要实现的逻辑：匹配同一ID对应的入库、出库日期，计算两个日期的差值，输出停留时长达到5天及以上的货件列表，结果表头为OVERSTAYED SHIPMENTS。

样例源数据 #

实现代码（通用SQL） #

用条件聚合分别提取每个货件的入、出库日期，再做日期差判断即可，该写法不受入/出库记录的存储顺序影响：

SELECT 
  ID AS `OVERSTAYED SHIPMENTS`
FROM (
  SELECT
    ID,
    MAX(CASE WHEN ARRIVAL = 'IN' THEN DATE END) AS in_date,
    MAX(CASE WHEN ARRIVAL = 'OUT' THEN DATE END) AS out_date
  FROM shipment_table
  GROUP BY ID
) AS shipment_duration
WHERE DATEDIFF(out_date, in_date) >= 5;

日期差函数适配提示：MySQL用DATEDIFF(结束日期, 开始日期)即可返回天数差；PostgreSQL、Oracle直接用out_date - in_date就能得到整数类型的天数间隔，无需调用额外函数。

执行结果 #

按样例数据计算：

• C1：2022-06-23出库、2022-06-18入库，停留5天，符合筛选条件
• C2：2022-06-20出库、2022-06-18入库，停留2天，不符合筛选条件
• C3：2022-06-24出库、2022-06-17入库，停留7天，符合筛选条件

最终输出的超期货件为C1、C3，和预期结果一致。

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