简单统计问题100分
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


------解决方案--------------------
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
------解决方案--------------------
接分~~~
小羊同学,能不能再想个更简单的 ?
------解决方案--------------------
不算复杂吧.
------解决方案--------------------
学下
------解决方案--------------------
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 行）

*/
------解决方案--------------------
？？
------解决方案--------------------
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

------解决方案--------------------
UP
------解决方案--------------------
SQL 2005 用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