TSQL - 下一个不同行的 LEAD

Question

有没有办法使用 lead 函数，这样我就可以在发生更改的地方获得下一行，而不是在相同的地方获得下一行？

在这个例子中，RowType 可以是"in"或"out"，对于每个"in"，我需要知道下一个 RowNumber 它变成了 "out"。我一直在使用铅功能，因为它非常快，但是我无法让它工作。我只需要执行以下操作，这是按当前行中不是的 RowType 分区。

select
RowNumber
,RowType --In this case I am only interested in RowType = 'In'
, Lead(RowNumber) 
    OVER (partition by "RowType = out" --This is the bit I am stuck on--
          order by  RowNumber ASC) as NextOutFlow
from table
order by RowNumber asc 

提前感谢您的任何帮助

Answer 1

与其使用

lead()，我会使用一个 outer apply，该为所有类型为 in 的行返回下一行类型为 out：

select RowNumber, RowType, nextOut 
from your_table t
outer apply (
  select min(RowNumber) as nextOut 
  from your_table 
  where RowNumber > t.RowNumber and RowType='Out'
) oa
where RowType = 'In'
order by RowNumber asc 

给定示例数据，例如：

RowNumber   RowType
1           in
2           out
3           in
4           in
5           out
6           in

这将返回：

RowNumber   RowType nextOut
1           in      2
3           in      5
4           in      5
6           in      NULL

Answer 2

我认为这会起作用
如果您使用位字段进行输入输出，您将获得更好的性能

;with cte1 as 
(
  SELECT [inden], [OnOff]
       , lag([OnOff]) over (order by [inden]) as [lagOnOff]
    FROM [OnOff] 
),   cte2 as
(
  select [inden], [OnOff], [lagOnOff]
       , lead([inden]) over (order by [inden]) as [Leadinden]
    from cte1 
   where [OnOff] <> [lagOnOff]
      or [lagOnOff] is null
 )
 select [inden], [OnOff], [lagOnOff], [Leadinden]
   from cte2 
  where [OnOff] = 'true'

可能更慢，但如果您有正确的索引可能会起作用

select t1.rowNum as 'rowNumIn', min(t2.rownum) as 'nextRowNumOut' 
  from tabel t1 
  join table t2 
    on t1.rowType = 'In' 
   and t2.rowType = 'Out'
   and t2.rowNum > t1.rowNum  
   and t2.rowNum < t1.rowNum + 1000  -- if you can constrain it 
 group by t1.rowNum