Simple statistical problem

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
--Requires that the result is the same for multiple adjacent classes, and the spendtime is added
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

Not too complicated.

smpqsmpq

Learn

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
--Inquire

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
--result
/ *
class spendtime
-------------------- -----------
A 1
B 3
A 2
B 2

(4 rows affected)

* /

prajna

Learn

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

Processing with 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