Simple statistical problem

yrasd11

TO:gtolishun()

Requires that the results are the same for multiple adjacent classes.

prajna

No upstairs, three or more in a row is not right


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

prajna

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 'B', 1 union all
select 'B', 2 union all
select 'A', 2 union all
select 'B', 2
go

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


--result
A 1
B 6
A 2
B 2

gtolishun

-Modify it.
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 class, spendtime = sum (spendtime), orderindex = max (orderindex) from #t a
where exists (select 1 from #t where class = a.class and orderindex = a.orderindex + 1)
group by class
) tb
on ta.class = tb.class

prajna

michealhenrydrops like me, hehe

michealhenry

Oh, just seeing your test results is different
Didn't see your test data different! Ha ha

Then I support you!

prajna

Haha ~