TSQL查询修正：筛选符合条件的最近审计记录的失效规则

筛选所有状态为Inactive（Rule_table.Status）的规则，要求其最近一条audit_text='status_change'的审计记录创建日期已超过6个月。示例中Rule_id 4的对应审计记录仅1个月，不应被选中。

以下是尝试的Microsoft SQL Server-T-SQL代码：

select R.Id, A.created_date from Rule R
join Rule_Audit A on R.id=A.id
where R.ID= (
  select top1 1 ID from Rule_Audit where Rule_Id=R.Rule_Id and created_date < dateadd 
   (MONTH, -7, GETDATE()) order by created_date desc
)
and R.status='Inactive'

该查询错误地返回了Rule id 4，需修正。

原查询存在两个核心问题：

1. 未过滤audit_text='status_change'的审计记录，会包含其他类型的审计数据；
2. 子查询逻辑是判断规则是否存在一条超过7个月的审计记录，而非最近一条状态变更记录是否超过6个月，导致不符合条件的Rule_id 4被错误返回。

方法一：使用窗口函数定位最近记录 #

WITH RecentStatusChanges AS (
    SELECT 
        Rule_Id,
        created_date,
        -- 按规则分组，按创建日期倒序排序，标记最近的记录为1
        ROW_NUMBER() OVER (PARTITION BY Rule_Id ORDER BY created_date DESC) AS rn
    FROM Rule_Audit
    WHERE audit_text = 'status_change' -- 仅筛选状态变更的审计记录
)
SELECT 
    R.Id,
    RSC.created_date AS latest_status_change_date
FROM Rule R
JOIN RecentStatusChanges RSC ON R.Id = RSC.Rule_Id
WHERE 
    R.status = 'Inactive'
    AND RSC.rn = 1 -- 取最近的一条状态变更记录
    AND RSC.created_date < DATEADD(MONTH, -6, GETDATE()) -- 日期超过6个月

方法二：使用聚合函数筛选最近记录 #

SELECT 
    R.Id,
    MAX(A.created_date) AS latest_status_change_date
FROM Rule R
JOIN Rule_Audit A ON R.Id = A.Rule_Id
WHERE 
    R.status = 'Inactive'
    AND A.audit_text = 'status_change' -- 仅筛选状态变更的审计记录
GROUP BY R.Id
-- 筛选最近的状态变更记录已超过6个月
HAVING MAX(A.created_date) < DATEADD(MONTH, -6, GETDATE())

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