TSQL需求：获取各id中status2行对应的最近前置status1的rowNum

样本数据 #

drop table if exists #temp
select * into #temp
from (
    values 
        ('id100', 'status1', 1), 
        ('id100', 'status2', 2), 
        ('id100', 'status1', 3), 
        ('id100', 'status0', 4), 
        ('id100', 'status2', 5), 
        ('id100', 'status2', 6), 
        ('id100', 'status1', 7), 
        ('id100', 'status1', 8), 
        ('id100', 'status2', 9), 
        ('id101', 'status1', 10), 
        ('id101', 'status2', 11)
) t(id, status, rowNum) 

需求说明 #

针对每个id中的status2行，返回其**最近的前置状态为status1**的rowNum；非status2行的value字段为空。期望返回结果如下：

尝试的错误代码 #

SELECT 
  t1.id, 
  t1.status, 
  t1.rowNum, 
  (
    select 
      MIN(t2.rowNum) 
    from 
      #temp t2 
    where 
      t2.id = t1.id 
      and t2.rowNum < t1.rowNum 
      and t1.status = 'status2'
  ) as test 
from 
  #temp t1

错误原因：

1. 子查询未过滤t2.status = 'status1'，导致返回所有比当前rowNum小的行，而非仅status1的行
2. 使用MIN(t2.rowNum)会拿到最早的前置行，而非最近的目标行（应使用MAX(t2.rowNum)）

正确的TSQL实现 #

方法1：修正后的关联子查询 #

SELECT 
    t1.id,
    t1.status,
    t1.rowNum,
    CASE WHEN t1.status = 'status2' THEN
        (
            SELECT MAX(t2.rowNum)
            FROM #temp t2
            WHERE t2.id = t1.id
              AND t2.rowNum < t1.rowNum
              AND t2.status = 'status1'
        )
    ELSE NULL END AS value
FROM #temp t1
ORDER BY t1.id, t1.rowNum

方法2：窗口函数（更高效，适合大数据量） #

利用LAST_VALUE结合条件窗口，仅保留status1的rowNum，按id分区、rowNum排序，直接获取最近的前置status1行：

SELECT 
    id,
    status,
    rowNum,
    CASE WHEN status = 'status2' THEN
        LAST_VALUE(CASE WHEN status = 'status1' THEN rowNum END) 
            OVER (PARTITION BY id ORDER BY rowNum ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    ELSE NULL END AS value
FROM #temp
ORDER BY id, rowNum

代码说明 #

• 方法1：仅在当前行是status2时，通过子查询找到同id下rowNum更小的所有status1行中的最大rowNum（即最近的前置行）
• 方法2：先对每行标记出status1对应的rowNum（非status1行标记为NULL），再通过LAST_VALUE在分区内向前查找最后一个非NULL值，即最近的前置status1的rowNum；最后仅在当前行是status2时显示该值，否则为空

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