按类别查找缺失序列

Question

我必须从下面的例子中找出丢失的记录。

Category    BatchNo TransactionNo
+++++++++++++++++++++++++++++++++
CAT1           1    1
CAT1           1    2
CAT1           2    3
CAT1           2    4
CAT1           2    5
CAT1           3    6
CAT1           3    7
CAT1           3    8
CAT1           5    12
CAT1           5    13
CAT1           5    14
CAT1           5    15
CAT1           7    18
CAT2           1    1
CAT2           1    2
CAT2           3    6
CAT2           3    7
CAT2           3    8
CAT2           3    9
CAT2           4    10
CAT2           4    11
CAT2           4    12
CAT2           6    14

我需要一个脚本来识别丢失的记录，如下所示

Category    BatchNo
+++++++++++++++++++
CAT1         4
CAT1         6
CAT2         2
CAT2         5

我不需要知道CAT1 8和CAT2 7是否存在，因为它们可能还没有被插入。

Answer 1

您可以为每个类别创建所有可能的批数不超过最大批次号的临时结果集，而不是选择不可用的批处理号。

create table TEMP(
        Category varchar(10),
        BatchNo int,
        TransactionNo int
    )
    insert into TEMP values
    ('CAT1', 1, 1),
    ('CAT1', 1, 2),
    ('CAT1', 2, 3),
    ('CAT1', 2, 4),
    ('CAT1', 2, 5),
    ('CAT1', 3, 6),
    ('CAT1', 3, 7),
    ('CAT1', 3, 8),
    ('CAT1', 5, 9),
    ('CAT1', 7, 10),
    ('CAT2', 1, 1),
    ('CAT2', 1, 2),
    ('CAT2', 3, 3),
    ('CAT2', 4, 4),
    ('CAT2', 4, 5),
    ('CAT2', 4, 6),
    ('CAT2', 6, 7);


    WITH BatchNo (BatchID,Category,MaxBatch) AS (
      SELECT 1, Category, MAX(BatchNo) AS MaxBatch  FROM TEMP GROUP BY Category
      UNION ALL
      SELECT BatchID + 1, Category, MaxBatch FROM BatchNo
      WHERE BatchID < MaxBatch
    )

    SELECT 
        BatchNo.Category,
        BatchNo.BatchID
    FROM 
        BatchNo
    WHERE 
        BatchID NOT IN (SELECT BatchNo FROM TEMP WHERE Category = BatchNo.Category)
    ORDER BY
        BatchNo.Category,
        BatchNo.BatchID


    DROP TABLE TEMP

Answer 2

这个使用Tally Table。参考：http://www.sqlservercentral.com/articles/T-SQL/62867/

样本数据

create table MyTable(
    Category varchar(10),
    BatchNo int,
    TransactionNo int
)
insert into MyTable values
('CAT1', 1, 1),
('CAT1', 1, 2),
('CAT1', 2, 3),
('CAT1', 2, 4),
('CAT1', 2, 5),
('CAT1', 3, 6),
('CAT1', 3, 7),
('CAT1', 3, 8),
('CAT1', 5, 12),
('CAT1', 5, 13),
('CAT1', 5, 14),
('CAT1', 5, 15),
('CAT1', 7, 18),
('CAT2', 1, 1),
('CAT2', 1, 2),
('CAT2', 3, 6),
('CAT2', 3, 7),
('CAT2', 3, 8),
('CAT2', 3, 9),
('CAT2', 4, 10),
('CAT2', 4, 11),
('CAT2', 4, 12),
('CAT2', 6, 14);

解决方案

with e1(n) as (
    select 1 union all select 1 union all select 1 union all 
    select 1 union all select 1 union all select 1 union all 
    select 1 union all select 1 union all select 1 union all select 1
),  --10e+1 or 10 rows
e2(n) as (select 1 from e1 a, e1 b), --10e+2 or 100 rows
e4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows
tally(n) as(
    select 
        top (select top 1 BatchNo from MyTable order by BatchNo desc)
        row_number() over(order by (select null))
    from e4
)
select  
    c.Category,
    t.n
from tally t
cross join(
    select 
        Category, 
        max(BatchNo) as MaxBatchNo 
    from MyTable 
    group by Category
)c
left join MyTable m
    on m.BatchNo = t.n
    and m.Category = c.Category
where
    m.Category is null 
    and t.n < c.MaxBatchNo
order by
    c.Category,
    t.n

Answer 3

最好创建一个投影表，并使用标准的left join查找空白：

declare @Sequencer table (
    Id int primary key
);

insert into @Sequencer (Id)
select top (1000) row_number() over(order by (select null)) from master.dbo.spt_values;

select *
from @Sequencer s
    inner join (
        select Category, max(BatchNo) as [Size] from dbo.Table group by Category
    ) cat on cat.Size > s.Id
    left join (
        select distinct Category, BatchNo from dbo.Table
    ) t on t.Category = cat.Category and t.BatchNo = s.Id
where t.BatchNo is null;

当然，在现实生活中，您可能需要超过1000行，因此相应地调整它。