如何编写SQL查询获取指定月份的所有活跃保单？

首先明确需求：筛选出1月份处于活跃状态的保单Id，活跃状态定义为：保单的最后一次状态变更结果是1，且该最后变更的时间在1月份或更早（后续无其他状态变更，确保1月期间保持活跃）。

方法1：使用窗口函数（推荐，简洁高效） #

利用ROW_NUMBER()窗口函数为每个保单的状态变更记录按时间倒序排序，取最新的那一条，再筛选符合条件的记录：

WITH LatestPolicyStatus AS (
    SELECT 
        IdPolicy,
        IdStatusChangedTo,
        DateChanged,
        ROW_NUMBER() OVER (PARTITION BY IdPolicy ORDER BY DateChanged DESC) AS rn
    FROM PolicyStatusLog
)
SELECT DISTINCT IdPolicy
FROM LatestPolicyStatus
WHERE rn = 1 
  AND IdStatusChangedTo = 1
  AND DATEPART(MONTH, DateChanged) = 1 -- 筛选最后变更发生在1月份
  -- 如果要包含1月及之前所有最后变更为1且无后续变更的，把上面的条件改成：DateChanged <= 'YYYY-01-31'
  -- 注意替换YYYY为目标年份，比如2020

方法2：使用子查询获取每个保单的最新变更时间 #

先找到每个保单的最新变更日期，再关联原表获取对应状态，最后筛选：

SELECT DISTINCT psl.IdPolicy
FROM PolicyStatusLog psl
INNER JOIN (
    SELECT IdPolicy, MAX(DateChanged) AS LastChangeDate
    FROM PolicyStatusLog
    GROUP BY IdPolicy
) latest ON psl.IdPolicy = latest.IdPolicy AND psl.DateChanged = latest.LastChangeDate
WHERE psl.IdStatusChangedTo = 1
  AND DATEPART(MONTH, psl.DateChanged) = 1 -- 或 DateChanged <= 'YYYY-01-31' 包含更早的变更

方法3：使用NOT EXISTS排除有后续变更的记录 #

通过判断不存在比当前记录更新的状态变更，来找到每个保单的最新记录：

SELECT DISTINCT psl.IdPolicy
FROM PolicyStatusLog psl
WHERE psl.IdStatusChangedTo = 1
  AND NOT EXISTS (
      SELECT 1
      FROM PolicyStatusLog psl2
      WHERE psl2.IdPolicy = psl.IdPolicy
        AND psl2.DateChanged > psl.DateChanged
  )
  AND DATEPART(MONTH, psl.DateChanged) = 1 -- 或 DateChanged <= 'YYYY-01-31'

说明 #

• 如果需求是只要最后一次变更为1，且该变更时间在1月或更早，请将日期条件替换为DateChanged <= 'YYYY-01-31'（替换YYYY为具体年份，比如2020），这样会包含所有在目标1月及之前最后一次变更为活跃且无后续状态变更的保单。
• 用DISTINCT是为了避免同一保单出现多条符合条件的记录（虽然逻辑上每个保单只会有一条最新记录，但加上更稳妥）。

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