关联transactions与reservations表，处理预订变更时的交易记录复制需求

要实现需求，需要覆盖两种场景：常规交易与预订的关联记录，以及预订变更时基于原交易生成的新记录，可以通过UNION ALL合并这两部分结果来完成。

完整SQL语句 #

-- 第一部分：常规关联的交易与预订记录
SELECT
  b."fiscalPeriod",
  b."purchaseId",
  b."charge",
  b."refund",
  a."checkIn",
  a."checkOut"
FROM transactions b
LEFT JOIN reservations a
  ON b."purchaseId" = a."purchaseId"
  AND b."fiscalPeriod" >= a."validFrom"
  AND b."fiscalPeriod" < a."validTo"

UNION ALL

-- 第二部分：预订变更时生成的新记录
SELECT
  a."changeDate" AS "fiscalPeriod",
  b."purchaseId",
  b."charge",
  b."refund",
  a."checkIn",
  a."checkOut"
FROM reservations a
JOIN (
  -- 子查询：获取每个purchaseId在变更日期前的最新交易记录
  SELECT
    "purchaseId",
    "charge",
    "refund",
    ROW_NUMBER() OVER (PARTITION BY "purchaseId" ORDER BY "fiscalPeriod" DESC) AS rn
  FROM transactions t
  WHERE t."fiscalPeriod" < a."changeDate"
) b ON a."purchaseId" = b."purchaseId" AND b.rn = 1
WHERE a."changeDate" IS NOT NULL; -- 仅处理存在变更的预订

逻辑说明 #

1. 常规关联部分

   • 以transactions为主表，左关联reservations，匹配条件为订单ID一致，且交易的会计周期在预订的有效期范围内。
   • 保留交易原有的fiscalPeriod，同时获取对应预订的入住/退房日期。

2. 预订变更处理部分

   • 针对有changeDate的预订记录，通过子查询找到该订单下变更日期之前的最新交易记录（用ROW_NUMBER()按会计周期倒序排序，取第一条）。
   • 复制该交易的charge和refund值，将fiscalPeriod替换为预订的changeDate，同时更新为新的入住/退房日期。

原查询问题修正 #

• 原查询使用FULL OUTER JOIN会引入不必要的空值记录，实际场景中应以交易记录为基础进行关联。
• 原查询未处理“复制交易记录生成新行”的核心逻辑，仅通过IFNULL修改会计周期，无法满足预订变更时生成新记录的需求。

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