选择第一行，其中接下来的 2 行增加 + 1 并且 (rowVal + 2) 可以被 3 整除 [英] Select first row where next 2 rows increment by + 1 and (rowVal + 2) is divisible by 3

问题描述

我已经准备好了@Gordon Linoff 部分回答了这个问题，还有一个额外的要求.

I all ready had this question partially answered by @Gordon Linoff there with was an additional requirements.

我想找到后续 n 行值按 + 1 递增的第一行.

I want to find the first row where the subsequent n rows values increment by + 1.

CREATE TABLE #Temp
    ([ID] int, [cct] int)
;

INSERT INTO #Temp
    ([ID], [cct])
VALUES
    (12807, 6),
    (12813, 12),
    (12818, 17),
    (12823, 22),
    (12824, 23),
    (12830, 29),
    (12831, 30),
    (12832, 31),
    (12833, 32),
    (12835, 34),
    (12837, 36),
    (12838, 37),
    (12839, 38),
    (12840, 39),
    (12841, 40),
    (12844, 43),
    (12846, 45),
    (12847, 46),
    (12848, 47),
    (12849, 48),
    (12850, 49),
    (12851, 50),
    (12854, 53),
    (12856, 55),
    (12857, 56),
    (12860, 59),
    (12862, 61),
    (12863, 62),
    (12864, 63),
    (12865, 64),
    (12866, 65),
    (12871, 70),
    (12872, 71),
    (12873, 72)
;

@Gordon 已经给我提供了这个代码来查找它的序列部分.

@Gordon already provided me with this code to find the sequence part of it.

  select min(id),min(cct) as cct, count(*) as length
from (select s.*, (cct - row_number() over (order by id)) as grp
      from #Temp s
     ) s
group by grp
having count(*) >= 3

这非常适合查找序列.从结果可以看出.

This works perfect to find the sequence. As can be seen from the result.

ID  cct length
12830   29  4
12837   36  5
12846   45  6
12862   61  5
12871   70  3

但我需要在满足 (cct + 2) % 3 = 0 的序列中找到 cct 值

but I need to find the cct value within the sequence that satisfies (cct + 2) % 3 = 0

我需要查询返回 ID 12838,cct 37，因为这是第一个 cct 值 + 2 可被 3 整除，接下来的 2 行值递增 1.

I need the query to return ID 12838,cct 37 as this is first cct value + 2 divisible by 3 that has the next 2 row values increment by 1.

任何帮助将不胜感激.

推荐答案

我想我已经明白你需要什么了，试试这个:

I think I have understood what you need, try this:

;with
grp as (-- get the sequences as @Gordon suggested
    select s.*, (cct - row_number() over (order by id)) as grp
    from #Temp s
),
grp_seq as (-- get the sequence position of each id
    select *, ROW_NUMBER() over (PARTITION by grp order by cct) n
    from grp
),
grp_min_max as (-- get sequence informations min/max ID, start cct and sequence length, for each group
    select grp, min(id) min_id, max(id) max_id, min(cct) as cct, count(*) as length
    from grp_seq s
    group by grp
    having count(*) >= 3
)
-- finally join all toghether to retrieve your result
select t1.ID, t1.cct, '--------->' col_sep, t1.n seq_pos, t2.ID ID_cct2, t2.cct cct_div3, t3.*
from grp_seq t1
inner join grp_seq t2 on (t1.grp=t2.grp) and (t1.cct = t2.cct-2) and (t2.cct % 3 = 0) 
inner join grp_min_max t3 on t3.grp = t1.grp
order by id

它应该能满足你的所有需求

it should get all you need

ID  cct col_sep seq_pos ID_cct2 cct_div3    grp min_id  max_id  cct length
12838   37  --------->  2   12840   39  25  12837   12841   36  5
12847   46  --------->  2   12849   48  28  12846   12851   45  6
12862   61  --------->  1   12864   63  34  12862   12866   61  5
12871   70  --------->  1   12873   72  38  12871   12873   70  3

我在结果记录的序列上添加了一些额外的信息(在 col_sep 列之后)，期望 ID 不能总是 cct + SomeValue，这样它将为您提供所有可用信息，剥离你不需要的东西.

I have added some extra info (afer col_sep column) on sequence on result record expecting that ID could not be always cct + SomeValue, in this way it will give you all information available, strip off what you do not need.

希望能帮到你

这篇关于选择第一行，其中接下来的 2 行增加 + 1 并且 (rowVal + 2) 可以被 3 整除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！