如何编写SQL查询筛选2020年12月变更为B1且当前仍为该状态的账户

你写的SQL核心问题是加了VALID_TO_DATE = '31/12/2100'的筛选条件，导致像示例中400这类首次B1变更记录不是最新有效记录的账户被直接过滤，所以结果不符合预期。

要同时满足你提的两个规则，只要保证两点：

• 账户当前最新状态为B1（存在VALID_TO_DATE = '31/12/2100'且状态为B1的记录）
• 账户第一次出现B1状态的变更时间在2020年12月

对应的SQL如下：

SELECT DISTINCT ACCOUNT_ID
FROM TABLE t1
WHERE 
  -- 规则1：账户当前最新状态是B1
  EXISTS (
    SELECT 1 
    FROM TABLE t_current
    WHERE t_current.ACCOUNT_ID = t1.ACCOUNT_ID
      AND t_current.VALID_TO_DATE = '31/12/2100'
      AND t_current.ACCOUNT_STATUS = 'B1'
  )
  -- 首次B1变更发生在2020年12月
  AND t1.ACCOUNT_STATUS = 'B1'
  AND TO_DATE(t1.ACCOUNT_CHANGE_DATE, 'DD/MM/YYYY') BETWEEN TO_DATE('01/12/2020', 'DD/MM/YYYY') AND TO_DATE('31/12/2020', 'DD/MM/YYYY')
  -- 规则2：此前没有出现过B1状态
  AND NOT EXISTS (
    SELECT 1 
    FROM TABLE t2
    WHERE t2.ACCOUNT_ID = t1.ACCOUNT_ID
      AND t2.ACCOUNT_STATUS = 'B1'
      AND TO_DATE(t2.ACCOUNT_CHANGE_DATE, 'DD/MM/YYYY') < TO_DATE(t1.ACCOUNT_CHANGE_DATE, 'DD/MM/YYYY')
  );

注：这里用TO_DATE做日期格式转换是为了避免字符串比较的逻辑错误，如果你使用的数据库原生支持DD/MM/YYYY格式的日期字符串直接比较，可以去掉转换函数，直接用原字段比较即可。

用你提供的示例数据执行上面的SQL，返回的ACCOUNT_ID就是100和400，完全符合预期：

• 100：首次B1变更时间为2020年12月15日，当前状态为B1
• 400：首次B1变更时间为2020年12月14日，当前状态保持B1
• 200：当前状态不是B1，被过滤
• 300：当前状态不是B1，且2020年11月就出现过B1状态后变更为其他状态，被过滤

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