如何对比同一事务不同记录的两个日期列 判断归还时间是否早于认领到期日

核心思路是将同一事务下的CLAIMED和RETURNED两条记录的对应字段合并到同一行，再进行时间对比即可，以下是两种常用实现方式：

方法1：自关联查询 #

直接关联同一张表，分别过滤两种状态的记录后关联对比：

SELECT 
  c.transaction_id,
  c.user_name,
  c.country,
  c.expiration_datetime AS claimed_expire_time,
  r.status_datetime_update AS returned_time,
  CASE 
    WHEN r.status_datetime_update <= c.expiration_datetime THEN '归还早于到期'
    ELSE '归还晚于到期'
  END AS compare_result
FROM TBL_A c
INNER JOIN TBL_A r
  ON c.transaction_id = r.transaction_id
  AND c.status = 'CLAIMED'
  AND r.status = 'RETURNED';

方法2：分组聚合查询 #

按事务ID分组，通过条件聚合提取两个需要对比的时间字段：

SELECT 
  transaction_id,
  MAX(user_name) AS user_name,
  MAX(country) AS country,
  MAX(CASE WHEN status = 'CLAIMED' THEN expiration_datetime END) AS claimed_expire_time,
  MAX(CASE WHEN status = 'RETURNED' THEN status_datetime_update END) AS returned_time,
  CASE 
    WHEN MAX(CASE WHEN status = 'RETURNED' THEN status_datetime_update END) <= MAX(CASE WHEN status = 'CLAIMED' THEN expiration_datetime END) THEN '归还早于到期'
    ELSE '归还晚于到期'
  END AS compare_result
FROM TBL_A
GROUP BY transaction_id;

查询结果验证 #

两种方法执行后都会得到如下结果，和预期一致：

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