对递增 1 的连续行进行分组 [英] Group consecutive rows that are incremented by 1

问题描述

这是我的数据:

FirstKey   SecondKey    Sequence
A1         B1           10001
A1         B1           10002
A1         B1           10003
A1         B1           10004
A1         B2           10001
A1         B2           10003
A1         B2           10005
A1         B3           10001
A1         B3           10002
A1         B3           10003
A2         B4           20001
A2         B5           20002
A2         B5           20003
A2         B6           20004

我尝试输出以下结果:

FirstKey   SecondKey    StartSequence EndSequence
A1         B1           10001         10004
A1         B2           10001         10001
A1         B2           10003         10003
A1         B2           10005         10005
A1         B3           10001         10003
A2         B4           20001         20001
A2         B5           20002         20003
A2         B6           20004         20004

Sequence 是一个整数，而不是一个 varchar.我希望具有相同键(FirstKey，SecondKey)的序列出现在同一行上.我的结果按 FirstKey、SecondKey、Sequence 排序有谁知道在 SQL 中输出这个的解决方案(我正在使用 Sybase 12.5)

Sequence is an integer, not a varchar. I want sequences that follow each other with the same key (FirstKey, SecondKey) to appear on the same row. My result is ordered by FirstKey, SecondKey, Sequence Does anyone know a solution to output this in SQL (I'm running with Sybase 12.5)

推荐答案

一个常见的解决方案是利用 ROW_NUMBER 和您的序列中存在间隙的事实:

A common solution utilizes a ROW_NUMBER and the fact that there are gaps in your sequence:

select FirstKey,
   SecondKey,
   min(Sequence),
   max(Sequence)
from 
 (
   select FirstKey,
      SecondKey,
      Sequence,
      -- meaningless value, but the same for sequential rows
      Sequence -
      row_number() over (partition by FirstKey, SecondKey order by Sequence) as grp 
   from tab
 ) dt
group by 
   FirstKey,
   SecondKey,
   grp

这篇关于对递增 1 的连续行进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！