如何在SQL Server中获取这个列结果

Looking to try to return the dataset below in SQL Server. I have the 2 columns TrailerPosition & Divider and looking to also return Zone. Zone would be calculated as starting with Zone 1 and then would change to zone 2 on the record after divider = 1. And then to 3 after the next record where Divider = 1. The screenshot below looks like the column I'm trying to return.

试图返回SQL Server下的数据集。我有2列拖车位置和分割机，也在寻找返回区。区域将计算为从第1区开始，然后在分隔符= 1后将更改到记录的区域2。然后到第三个记录，分割线= 1。下面的截图看起来像是我要返回的那一列。

any ideas how this can be done in SQL Server?

有什么想法可以在SQL Server中实现吗?

Test data for the below:

以下测试数据:

declare @t table (TrailerPosition nvarchar(5),Divider bit);
insert into @t values ('01L',0),('01R',0),('02L',0),('02R',1),('03L',1),('03R',0),('04L',0),('04R',0),('05L',1),('05R',1),('06L',0),('06R',0),('07L',0),('07R',0),('08L',0),('08R',0),('09L',0),('09R',0),('10L',0),('10R',0),('11L',0),('11R',0),('12L',0),('12R',0),('13L',0),('13R',0),('14L',0),('14R',0),('15L',0),('15R',0);

2 个解决方案

#1

If 2012+, the window functions would be a nice fit here

如果是2012年，窗口函数将会非常适合这里。

Select TrailerPosition
      ,Divider
      ,Zone    = 1+sum(Flag) over (Order By TrailerPosition)
 From (
        Select *
              ,Flag = case when Lag(Divider,1) over (Order By TrailerPosition) =1 and Divider=0 then 1 else 0 end
         From  YourTable
      ) A

Returns

#2

So, the Zone = 1 + The number of previous rows with the divider value of 0 and the previous row having a divider value of 1.

因此，Zone = 1 +之前的行数，除数为0，前一行的除数为1。

UPDATED

更新

SELECT TrailerPosition, Divider,
     (SELECT COUNT(*) 
     FROM @MyTable T1
     WHERE (T1.TrailerPosition <= t0.TrailerPosition)
        AND (T1.Divider = 0)        
        AND (SELECT Divider 
             FROM @MyTable t2
             WHERE T2.TrailerPosition = 
                 (SELECT MAX(T3.TrailerPosition) 
                  FROM @MyTable T3 
                  WHERE T3.TrailerPosition < T1.TrailerPosition)) = 1) + 1 
                     AS Zone
 FROM @MyTable t0

#1