简单统计问题

yrasd11

create table #t (class varchar(20),spendtime int,orderindex int identity)
insert #t
select 'A',1 union all
select 'B',1 union all
select 'B',2 union all
select 'A',2 union all
select 'B',2
select * from #t
--要求结果相邻多个class相同，spendtime相加
A   1
B   3
A   2
B   2
--drop table #t

23646766

select *, 0 refindex  into #a from #t

declare @class varchar(10),@spendtime int,@refindex int
select @class='',@spendtime=0,@refindex=0

update #a set @spendtime=case when class=@class then @spendtime+spendtime else spendtime end
        ,spendtime=@spendtime,@refindex=case when class=@class then @refindex else @refindex+1 end
        ,refindex=@refindex,@class=class

select class,max(spendtime)
from #a
group by class,refindex

unkmass

不算复杂吧.

smpqsmpq

学下

gtolishun

create table #t (class varchar(20),spendtime int,orderindex int identity)
insert #t
select 'A',1 union all
select 'B',1 union all
select 'B',2 union all
select 'A',2 union all
select 'B',2
--查询

select ta.class, spendtime=case when  ta.orderindex=tb.orderindex+1
                      then isnull(ta.spendtime,0)+isnull(tb.spendtime,0)
                    else isnull(ta.spendtime,0) end
from
(
        select * from #t a
        where not exists (select 1 from #t where class=a.class and orderindex=a.orderindex+1)
) ta
left join
(
        select * from #t a
        where   exists (select 1 from #t where class=a.class and orderindex=a.orderindex+1)
) tb
on ta.class=tb.class
--结果
/*
class                spendtime   
-------------------- -----------
A                    1
B                    3
A                    2
B                    2

（所影响的行数为 4 行）

*/

prajna

学习

prajna

？？

michealhenry

select class,sum(spendtime) as spendtime
from
(
select class,spendtime,
orderindex = (select isnull(max(orderindex),0) + 1 from #t where orderindex < a.orderindex and class <> a.class)
from #t a
)tt
group by class,orderindex


A        1
B        3
A        2
B        2

lilily

UP

23646766

用CTE处理：
with temp (class,spendtime,orderindex,refindex)
as
(
select *,orderindex from #t a
        where not exists (select 1 from #t where class=a.class and orderindex=a.orderindex-1)

union all

select a.class,a.spendtime,a.orderindex,b.refindex from #t a,temp b
where a.class=b.class and a.orderindex=b.orderindex+1
)

select class,sum(spendtime) as spendtime from temp group by class,refindex