简单统计问题

yrasd11

TO: gtolishun ( )

要求结果相邻多个class相同，spendtime相加

prajna

楼上不对，连续三个以上就不对了


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

--修改下啊.
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

michealhenry和我一样滴，嘿嘿

michealhenry

哦，光看到你的测试结果不同乐
没有看到你的测试数据不一样！呵呵

那我支持你把！

prajna

呵呵~