sql - 查找丢失的记录并返回相邻的记录 SQL

Question

我需要在原生 SQL 中将相邻记录返回到序列中缺失的记录。如果序列中的第一个条目丢失，则只返回下一个条目。无需在序列中寻找缺失的一端。

这是在 SQL Server 12.0.2000.8 中运行

相关列的结构:

BatchId(nvarchar(50), null) 
CreateDate(datetime, null)
UserId(varchar(50), null) 
Batch(varchar(50), null)

BatchId 中“-”后的最后一个数字确定顺序。 BatchId 与批处理相关联。当批处理更改时，BatchId 上的序列应重置为 1。

BatchId         CreateDate              UserId      Batch
#########################################################
9K182855 - 1    2017-09-26 17:57:20.977 9K182855    8
9K182855 - 2    2017-09-26 18:20:57.693 9K182855    8
9K182855 - 1    2017-09-27 11:04:46.177 9K182855    9
9K182855 - 2    2017-09-27 11:19:32.990 9K182855    9

我用来获取数据的查询

select BatchID, CreateDate, UserId, Batch from Results
where CreateDate > dateadd(day,-2,getdate())
and Batch between 0 and 9
order by UserId, CreateDate, Batch;

这是好数据

BatchId         CreateDate              UserId      Batch
#########################################################
4L182855 - 1    2017-09-28 14:04:46.177 4L182855    9
4L182855 - 2    2017-09-28 15:19:32.990 4L182855    9
4L182855 - 3    2017-09-28 16:30:27.953 4L182855    9
4L182855 - 4    2017-09-28 17:57:20.977 4L182855    9
4L182855 - 5    2017-09-28 18:20:57.693 4L182855    9
4L182855 - 1    2017-09-29 11:04:46.177 4L182855    0
4L182855 - 2    2017-09-29 11:19:32.990 4L182855    0
4L182855 - 3    2017-09-29 11:30:27.953 4L182855    0
4L182855 - 4    2017-09-29 11:57:20.977 4L182855    0
4L182855 - 5    2017-09-29 12:00:57.693 4L182855    0
4L182855 - 6    2017-09-29 12:04:46.177 4L182855    0
4L182855 - 7    2017-09-29 12:19:32.990 4L182855    0
4L182855 - 8    2017-09-29 12:30:27.953 4L182855    0
4L182855 - 9    2017-09-29 13:57:20.977 4L182855    0
4L182855 - 10   2017-09-29 14:20:57.693 4L182855    0

这是缺失数据

BatchId         CreateDate              UserId      Batch
#########################################################
4L182855 - 1    2017-09-28 14:04:46.177 4L182855    9
4L182855 - 2    2017-09-28 15:19:32.990 4L182855    9
4L182855 - 4    2017-09-28 17:57:20.977 4L182855    9
4L182855 - 5    2017-09-28 18:20:57.693 4L182855    9
4L182855 - 1    2017-09-29 11:04:46.177 4L182855    0
4L182855 - 2    2017-09-29 11:19:32.990 4L182855    0
4L182855 - 3    2017-09-29 11:30:27.953 4L182855    0
4L182855 - 4    2017-09-29 11:57:20.977 4L182855    0
4L182855 - 5    2017-09-29 12:00:57.693 4L182855    0
4L182855 - 6    2017-09-29 12:04:46.177 4L182855    0
4L182855 - 7    2017-09-29 12:19:32.990 4L182855    0
4L182855 - 8    2017-09-29 12:30:27.953 4L182855    0
4L182855 - 10   2017-09-29 14:20:57.693 4L182855    0

要求是返回下面的行，它们与缺失的记录相邻

BatchId         CreateDate              UserId      Batch
#########################################################
4L182855 - 2    2017-09-28 15:19:32.990 4L182855    9
4L182855 - 4    2017-09-28 17:57:20.977 4L182855    9
4L182855 - 8    2017-09-29 12:30:27.953 4L182855    0
4L182855 - 10   2017-09-29 14:20:57.693 4L182855    0

我可以用 Python 或可能通过 CLR 用户定义函数来完成此操作。但是，我不确定它在 native SQL 中是否可行。可以的话请赐教。

Answer 1

使用stuff() 截断batchid 得到批处理序列，lead()和 lag()为计算的 BatchSeq 获取上一行和下一行的值:

select s.BatchId, s.CreateDate, s.UserId, s.Batch
from (
  select t.*
    , PrevSeq = lag(x.BatchSeq)  over (partition by Batch order by CreateDate)
    , x.BatchSeq
    , NextSeq = lead(x.BatchSeq) over (order by CreateDate)
  from results t
    cross apply (values (convert(int,stuff(t.batchid,1,charindex('- ',t.batchid)+1,'')))
      ) x (BatchSeq)
  ) s
where BatchSeq - isnull(PrevSeq,0) != 1 
  or (BatchSeq - NextSeq !=-1 and NextSeq != 1)
order by createdate

rextester 演示:http://rextester.com/ZCBLP37968

返回:

+---------------+---------------------+----------+-------+
|    BatchId    |     CreateDate      |  UserId  | Batch |
+---------------+---------------------+----------+-------+
| 4L182855 - 2  | 2017-09-28 15:19:32 | 4L182855 |     9 |
| 4L182855 - 4  | 2017-09-28 17:57:20 | 4L182855 |     9 |
| 4L182855 - 8  | 2017-09-29 12:30:27 | 4L182855 |     0 |
| 4L182855 - 10 | 2017-09-29 14:20:57 | 4L182855 |     0 |
+---------------+---------------------+----------+-------+

---

这也适用于缺少的第一条记录:http://rextester.com/BLAD55913