简单统计问题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
--结果
A1
B3
A2
B2
[解决办法]
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
)
select class,sum(spendtime) as spendtime from temp group by class,refindex
[解决办法]
楼上不对，连续三个以上就不对了
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
[解决办法]
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
A1
B6
A2
B2
[解决办法]
--修改下啊.
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
