遞增分配編號，值更改時重置

September 2, 2020

declare @t table (scanCode varchar(6), dates datetime, flag varchar(1))

insert @t ( scanCode, dates, flag)
select '182086','01 Jul 2020','P' union all
select'182086','02 Jul 2020','P' union all
select'182086','03 Jul 2020','A' union all
select'182086','04 Jul 2020','A' union all
select'182086','06 Jul 2020','P' union all
select'182086','07 Jul 2020','P' union all
select'182086','08 Jul 2020','P' union all
select'182086','09 Jul 2020','P' union all
select'182086','10 Jul 2020','A' union all
select'182086','11 Jul 2020','A' union all
select'182086','13 Jul 2020','A' union all
select'182086','14 Jul 2020','A'


select scanCode
   , dates
   , flag
   , prn = row_number() over (partition by scanCode,flag order by scanCode, dates) 
from @t t
order by t.dates

目前的結果是：

scanCode    dates   flag    prn
182086      2020/07/01  P   1
182086      2020/07/02  P   2
182086      2020/07/03  A   1
182086      2020/07/04  A   2
182086      2020/07/06  P   3
182086      2020/07/07  P   4
182086      2020/07/08  P   5
182086      2020/07/09  P   6
182086      2020/07/10  A   3
182086      2020/07/11  A   4
182086      2020/07/13  A   5
182086      2020/07/14  A   6

我希望結果是：

scanCode    dates   flag    prn
182086      2020/07/01  P   1
182086      2020/07/02  P   2
182086      2020/07/03  A   1
182086      2020/07/04  A   2
182086      2020/07/06  P   1
182086      2020/07/07  P   2
182086      2020/07/08  P   3
182086      2020/07/09  P   4
182086      2020/07/10  A   1
182086      2020/07/11  A   2
182086      2020/07/13  A   3
182086      2020/07/14  A   4

scancode,dates是我表中的主鍵。

SQL Server 2008R2 的視窗功能非常有限，沒有滯後/超前功能，並且ORDER BY只能與ROW_NUMBER(). 所以解決這個問題的常用技術並不容易獲得。
我將假設您的主鍵是(ScanCode,Dates)，如果不是，則不會返回一致的結果。
SELECT
 curr.ScanCode
,curr.Dates
,curr.Flag
,prn = ROW_NUMBER() OVER (PARTITION BY curr.ScanCode, pr.Dates ORDER BY curr.Dates)
FROM
 @t curr
LEFT JOIN
 @t pr --Get maximum date prior to current date where Flag did not match
   ON pr.ScanCode = curr.ScanCode
       AND pr.Dates =
         (
           SELECT
             MAX(Dates)
           FROM
             @t
           WHERE
             ScanCode = curr.ScanCode
               AND Flag &lt;&gt; curr.Flag
               AND Dates &lt; curr.Dates
         )
這是有效的，因為連續塊的日期Flag都將具有相同的先前日期值：
ScanCode    Dates       Flag    PriorDate
182086      2020-07-01  P       NULL
182086      2020-07-02  P       NULL
182086      2020-07-03  A       2020-07-02
182086      2020-07-04  A       2020-07-02
182086      2020-07-06  P       2020-07-04
182086      2020-07-07  P       2020-07-04
182086      2020-07-08  P       2020-07-04
182086      2020-07-09  P       2020-07-04
182086      2020-07-10  A       2020-07-09
182086      2020-07-11  A       2020-07-09
182086      2020-07-13  A       2020-07-09
182086      2020-07-14  A       2020-07-09
因此，如果您的聚群索引選擇正確，這是一次表掃描加上每行兩次查找，並且為PARTITION BY. 不是很好，但即使使用 LAG/LEAD，您也可能不會創建任何更有效的東西。
其他建議：
Dates不是一個好的列名，因為該列儲存單個值，而不是複數。此外，它不能很好地描述該列所代表的內容 - 這是某個事件的日期嗎？入境日期？如果您可以重命名它，請執行此操作。此外，如果值始終是日期，請使用DATE數據類型。
這個實例中的主鍵真的應該是(ScanCode,Dates)如果它還沒有的話。這不僅是保證結果一致所必需的，而且獲取先前值的連接將是對聚集索引的查找。要進行測試，請在表變數上聲明一個主鍵，並比較有無執行計劃。

解決此問題的另一種方法是將其視為間隙和孤島模式的變體：

SELECT *, diff = (N.rn1 - N.rn2)
FROM 
(
   SELECT 
       *, 
       rn1 = ROW_NUMBER() OVER (
           PARTITION BY T.scanCode 
           ORDER BY T.dates),
       rn2 = ROW_NUMBER() OVER (
           PARTITION BY T.scanCode 
           ORDER BY T.flag, T.dates)
   FROM @t AS T
) AS N;

該輸出為我們提供了一種辨識組的方法：

剩下要做的就是對每個組中的行數進行編號。完整的查詢是：

WITH
   N AS 
   (
       -- Previous code
       SELECT 
           *, 
           rn1 = ROW_NUMBER() OVER (
               PARTITION BY T.scanCode 
               ORDER BY T.dates),
           rn2 = ROW_NUMBER() OVER (
               PARTITION BY T.scanCode 
               ORDER BY T.flag, T.dates)
       FROM @t AS T
   )
SELECT
   N.scanCode,
   N.dates,
   N.flag,
   prn = ROW_NUMBER() OVER (
       PARTITION BY N.scanCode, (N.rn1 - N.rn2)
       ORDER BY N.dates)
FROM N;

輸出是：