SQL Server查询SiteTransactions表获取未删除活跃站点组合的方法

• 原有查询仅判断了Create操作的时间晚于同组同名站点的任意历史事务，没有校验该Create操作之后是否存在对应Delete操作，因此会返回「创建后已被删除」的站点记录，不符合筛选活跃站点的需求。
• 业务规则允许删除后重建同名站点，因此每个SiteGroup+SiteName组合的最新一条事务类型直接决定当前状态：最新事务为Create则活跃，为Delete则已删除。

直接对每个组合的事务按时间倒序排序，取最新一条事务，筛选类型为Create的即可：

WITH RankedTransactions AS (
    SELECT 
        SiteName,
        SiteGroup,
        TransactionType,
        TransactionTime,
        -- 同组同站点的事务按时间倒序排名，最新的排第1
        ROW_NUMBER() OVER (PARTITION BY SiteGroup, SiteName ORDER BY TransactionTime DESC) AS rn
    FROM SiteTransactions
)
SELECT SiteName, SiteGroup, TransactionType, TransactionTime
FROM RankedTransactions
WHERE rn = 1 -- 取每个组合的最新事务
  AND TransactionType = 'Create' -- 最新事务为创建则为活跃
ORDER BY TransactionTime DESC

如果需要兼容不支持CTE和窗口函数的老版本SQL Server，可以修改原自关联逻辑，判断不存在比当前Create操作更晚的同组同名站点删除操作即可：

SELECT DISTINCT A.SiteName, A.SiteGroup, A.TransactionType, A.TransactionTime
FROM SiteTransactions A
WHERE A.TransactionType = 'Create'
  -- 不存在同组同站点、时间比当前创建时间更晚的删除操作
  AND NOT EXISTS (
      SELECT 1 
      FROM SiteTransactions B 
      WHERE B.SiteGroup = A.SiteGroup 
        AND B.SiteName = A.SiteName
        AND B.TransactionType = 'Delete'
        AND B.TransactionTime > A.TransactionTime
  )
ORDER BY A.TransactionTime DESC

内容的提问来源于stack exchange，提问作者Mr.Human