SQL条件查询逻辑验证：判断指定日期的活跃订阅

场景与表结构 #

现有两张关联SQL表，需实现指定日期的活跃订阅查询：

• MAIN表：存储订阅的起止日期与当前状态，已取消的订阅仅显示原到期日，无实际取消日期
• HISTORY表：记录所有订阅的状态变更（包括取消操作）

表结构具体如下：

MAIN表 #

HISTORY表 #

查询需求 #

筛选指定日期的活跃订阅，规则为：

1. 先从MAIN表筛选起止日期包含指定日期的订阅
2. 若订阅状态为Canceled，需关联HISTORY表，仅当实际取消日期晚于指定日期时，该订阅才被判定为活跃

写法1：UNION语句（以指定日期2022-1-1为例） #

(SELECT subscription_id from Main
WHERE status = 'Active' AND start <= '2022-1-1' AND end >= '2022-1-1')
UNION
(SELECT m.subscription_id from Main m
JOIN
(SELECT * from History
WHERE status = 'Canceled' AND date > '2022-1-1') h
ON m.subscription_id = h.subscription_id
WHERE m.status = 'Canceled' AND start <= '2022-1-1' AND end >= '2022-1-1')

逻辑验证 #

• 第一个查询块：直接筛选MAIN表中状态为Active、且指定日期在订阅起止范围内的记录，完全符合需求中"通常从MAIN表取值"的规则，逻辑正确。
• 第二个查询块：针对MAIN表中状态为Canceled的订阅，先关联HISTORY表中取消日期晚于指定日期的记录，再筛选MAIN表起止日期包含指定日期的订阅，完美匹配需求中"已取消订阅需取消日期晚于指定日期才选中"的要求。
• 用UNION合并结果，可自动去重（虽然两个查询块的条件互斥，不会出现重复，但UNION的使用不影响逻辑正确性）。
• 测试数据验证：指定日期2022-1-1时，订阅1会被第一个查询块选中；订阅2的取消日期为2021-4-1（早于指定日期），不会被第二个查询块选中，最终结果只有订阅1，符合实际业务逻辑（订阅2在2021-4-1已取消，2022-1-1时非活跃）。

写法2：基于CTE重构MAIN表 #

WITH cte AS (SELECT * FROM history WHERE status='Canceled') 
SELECT m.subscription_id, m.status, start,
(CASE WHEN m.status='Canceled' THEN cte.date else m.end) as end
FROM main m
LEFT JOIN cte ON m.subscription_id = cte.subscription_id

逻辑验证 #

• 该写法的核心思路是将已取消订阅的原到期日替换为实际取消日期，这个思路是符合需求的，但原写法缺少关键的筛选条件，无法直接得到符合要求的活跃订阅结果。
• 若要满足需求，需在原写法基础上添加筛选条件：

  WITH cte AS (SELECT * FROM history WHERE status='Canceled') 
  SELECT m.subscription_id, m.status, start,
  (CASE WHEN m.status='Canceled' THEN cte.date else m.end) as end
  FROM main m
  LEFT JOIN cte ON m.subscription_id = cte.subscription_id
  WHERE start <= '2022-1-1' 
    AND COALESCE(CASE WHEN m.status='Canceled' THEN cte.date ELSE m.end END, m.end) >= '2022-1-1'
  

• 补充筛选条件后，逻辑就完全符合需求：对于Active订阅，用原到期日判断；对于Canceled订阅，用实际取消日期判断，只要指定日期在start到重构后的end之间，就判定为活跃。
• 原写法的问题：仅完成了字段重构，没有筛选指定日期是否在有效范围内，直接运行会返回所有订阅，不符合查询需求。

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