问题

在数据表中，数据可以按某列分组，另有一列保存了序列号，每组中的序列号应该是连续+1的整数，但是因为某种原因，其中可能有些序列号会缺失，需要找出缺失的序列号，并输出其后的行作为定位依据。

问题场景建立

Create table #cost (group_cost char(10), PP_Seq int, date1 datetime, ST char(2))
GO
insert into #cost values ('T1',5,'2023-01-01','A')
insert into #cost values ('T1',6,'2023-02-01','A')
insert into #cost values ('T1',7,'2023-03-01','A')

insert into #cost values ('T2',12,'2023-01-01','A')
insert into #cost values ('T2',14,'2023-02-01','B')  -- This pp_seq is not increment by 1. Should be in output 
insert into #cost values ('T2',15,'2023-03-01','A')
insert into #cost values ('T2',17,'2023-03-01','A')  -- This pp_seq is not increment by 1. Should be in output 
insert into #cost values ('T2',19,'2023-03-01','A')  -- This pp_seq is not increment by 1. Should be in output 
insert into #cost values ('T2',20,'2023-03-01','A')

insert into #cost values ('T3',25,'2023-01-02','A')
insert into #cost values ('T3',26,'2023-02-01','A')
insert into #cost values ('T3',27,'2023-03-05','A')

insert into #cost values ('T5',65,'2023-01-01','A')
insert into #cost values ('T5',66,'2023-02-06','A')
insert into #cost values ('T5',67,'2023-03-04','W')
insert into #cost values ('T5',69,'2023-04-01','A') -- This pp_seq is not increment by 1. Should be in output 
GO

期望的结果

group_cost    PP_Seq      date1                ST        Comments
T2                  14             2023-02-01       B          PP_Seq is not in seq. Also check next rows to correct. 
T2                  17             2023-03-01       A          PP_Seq is not in seq. Also check next rows to correct. 
T2                  19             2023-03-01       A          PP_Seq is not in seq. Also check next rows to correct. 
T5                  69             2023-04-01       A          PP_Seq is not in seq. Also check next rows to correct. 

解决思路

1. 增加一列，使用ROW_NUMBER为此列填充新的按group_cost分组的连续+1序列号，作为正确序列号的参照，命名为PP_seq_correct；
2. 计算每行PP_seq和PP_seq_correct的差值，并取每个group_cost分组序列中第一行的差值为基准值；
3. 过滤出每个分组中差值与基准值不同的行，这些都是异常行；
4. 将异常行按group_cost及差值再用GROUP BY分组，找出此分组的第一行，即为所需结果集。

实现代码如下

WITH T (group_cost, PP_Seq, PP_seq_correct, diff) AS
(
  SELECT c.group_cost, c.PP_seq, a.PP_seq_correct, c.PP_seq-a.PP_seq_correct AS diff 
  FROM #Cost c
  INNER JOIN
  (
    SELECT group_cost, PP_Seq, ROW_NUMBER() OVER(PARTITION BY group_cost ORDER BY PP_seq) AS PP_seq_correct FROM #Cost
  ) a
  ON c.group_cost=a.group_cost AND c.PP_seq=a.PP_seq
)
SELECT Co.*, 'Comments...' Comments FROM #cost Co INNER JOIN
(
  SELECT T.group_cost, Min(PP_Seq) PP_Seq FROM T LEFT JOIN
  (
    SELECT T.group_cost, T.diff FROM T WHERE T.PP_seq_correct=1
  ) D
  ON T.group_cost=D.group_cost AND T.diff=D.Diff
  WHERE D.diff IS NULL
  GROUP BY T.group_cost, T.diff
)R
ON Co.group_cost=R.group_cost AND Co.PP_Seq=R.PP_Seq

执行结果

group_cost PP_Seq      date1                   ST   Comments
---------- ----------- ----------------------- ---- -----------
T2         14          2023-02-01 00:00:00.000 B    Comments...
T2         17          2023-03-01 00:00:00.000 A    Comments...
T2         19          2023-03-01 00:00:00.000 A    Comments...
T5         69          2023-04-01 00:00:00.000 A    Comments...

结果符合要求