SQL查询基于序列

Question

分组结果我有这样的一个表：

ID Seq Amt 
1 1 500 
1 2 500 
1 3 500 
1 5 500 
2 10 600 
2 11 600 
3 1 700 
3 3 700 

我想组连续的序列号为一行是这样的：

ID Start End TotalAmt 
1 1  3 1500 
1 5  5 500 
2 10  11 1200 
3 1  1 700 
3 3  3 700 

请帮助达到这个结果。

Answer 1

WITH numbered AS (
    SELECT 
    ID, Seq, Amt, 
    SeqGroup = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Seq) - Seq 
    FROM atable 
) 
SELECT 
    ID, 
    Start = MIN(Seq), 
    [End] = MAX(Seq), 
    TotalAmt = SUM(Amt) 
FROM numbered 
GROUP BY ID, SeqGroup 
ORDER BY ID, Start 
; 

Answer 2

请尝试以下查询。

select id, min(seq), max(seq), sum(amt) from table group by id 

哎呀，对不起，这是错误的查询，因为你需要序列

Answer 3

这似乎很好地工作。 @breakingRows将包含破坏id和seq序列的所有行（即如果id改变或者如果seq不比以前的seq多1）。使用该表格，您可以在@temp之内选择这样的序列的所有行。但是我必须补充说，由于所有的子查询，性能可能并不是那么好，但是你需要测试以确定。

declare @temp table (id int, seq int, amt int) 
insert into @temp select 1, 1, 500 
insert into @temp select 1, 2, 500 
insert into @temp select 1, 3, 500 
insert into @temp select 1, 5, 500 
insert into @temp select 2, 10, 600 
insert into @temp select 2, 11, 600 
insert into @temp select 3, 1, 700 
insert into @temp select 3, 3, 700 

declare @breakingRows table (ctr int identity(1,1), id int, seq int) 

insert into @breakingRows(id, seq) 
select id, seq 
from @temp t1 
where not exists 
    (select 1 from @temp t2 where t1.id = t2.id and t1.seq - 1 = t2.seq) 
order by id, seq 

select br.id, br.seq as start, 
     isnull ((select top 1 seq from @temp t2 
       where id < (select id from @breakingRows br2 where br.ctr = br2.ctr - 1) or 
        (id = (select id from @breakingRows br2 where br.ctr = br2.ctr - 1) and 
         seq < (select seq from @breakingRows br2 where br.ctr = br2.ctr - 1))   
       order by id desc, seq desc), 
       br.seq) 
     as [end], 
     (select SUM(amt) from @temp t1 where t1.id = br.id and 
     t1.seq < 
      isnull((select seq from @breakingRows br2 where br.ctr = br2.ctr - 1 and br.id = br2.id), 
        (select max(seq) + 1 from @temp)) and 
     t1.seq >= br.seq) 
from @breakingRows br 
order by id, seq 

Answer 4

嗯，有可能是一个更优雅的方式来做到这一点（的东西我有提示），但这里的一种方法，将如果你使用一个版本的SQL Server接受公共表表达式工作：

use Tempdb 
go 

create table [Test] 
(
    [id] int not null, 
    [Seq] int not null, 
    [Amt] int not null 
) 

insert into [Test] values 
(1, 1, 500), 
(1, 2, 500), 
(1, 3, 500), 
(1, 5, 500), 
(2, 10, 600), 
(2, 11, 600), 
(3, 1, 700), 
(3, 3, 700) 

;with 
lower_bound as (
    select * 
     from Test 
    where not exists (
     select * 
      from Test as t1 
     where t1.id = Test.id and t1.Seq = Test.Seq - 1 
    ) 
), 
upper_bound as (
    select * 
     from Test 
    where not exists (
     select * 
      from Test as t1 
     where t1.id = Test.id and t1.Seq = Test.Seq + 1 
    ) 
), 
bounds as (
    select id, (select MAX(seq) from lower_bound where lower_bound.id = upper_bound.id and lower_bound.Seq <= upper_bound.Seq) as LBound, Seq as Ubound 
     from upper_bound 
) 
select Test.id, LBound As [Start], UBound As [End], SUM(Amt) As TotalAmt 
    from Test 
    join bounds 
    on Test.id = bounds.id 
    and Test.Seq between bounds.LBound and bounds.Ubound 
group by Test.id, LBound, UBound 

drop table [Test] 

Answer 5

由于舍甫琴科已经贴金溶液，这里是使用UPDATE语句来得到一个临时表的结果我取，只是为了好玩。

declare @tmp table (
    id int, seq int, amt money, start int, this int, total money, 
    primary key clustered(id, seq)) 
; 
insert @tmp 
select *, start=seq, this=seq, total=convert(money,amt) 
from btable 
; 
declare @id int, @seq int, @start int, @amt money 
update @tmp 
set 
    @amt = total = case when id = @id and seq = @seq+1 then @amt+total else amt end, 
    @start = start = case when id = @id and seq = @seq+1 then @start else seq end, 
    @seq = this = seq, 
    @id = id = id 
from @tmp 
option (maxdop 1) 
; 
select id, start, max(this) [end], max(total) total 
from @tmp 
group by id, start 
order by id, start 

注：

• BTABLE：你的表名
• ID INT，SEQ INT，AMT钱：在你的表所需的列