求一组连续数列的算法

[hhiew]

有一列数只有二个值，W和L，
如果值是W，如果W上一个值也是W，则记200，如果上面是L，则记：（2 的 L数量次方）*200，L要连续的，如果遇到W则不再往上推。
如果L，那上一个值是W，则简单的记录-200，如果上面是L，则算上本身，（2 的 L数量次方）*-200，L要连续的，如果遇到W则不再往上推。
w
W
L
w
L
W
L
L
W
W
--
W 200
W 200
L -200
w 400
L -200
W 400
L -200
L -400
L -800
W 1600
W 200
路位的能人帮个忙。谢谢

[开着拖拉机泡妞]

等大牛来解决

[叶子]

--关于次数问题我没有看明白

declare @t table (col varchar(1))
insert into @t
select 'w' union all
select 'w' union all
select 'l' union all
select 'w' union all
select 'l' union all
select 'w' union all
select 'l' union all
select 'l' union all
select 'l' union all
select 'w' union all
select 'w'

; with maco as 
(
select row_number() over (order by getdate()) as rid,* from @t
)

select a.col,
	case when isnull(b.col,'w')='w' and a.col='w' then 200
	when b.col='w' and a.col='l' then -200 end as col2
from maco a
left join maco b on a.rid=b.rid+1
/*
col  col2
---- -----------
w    200
w    200
l    -200
w    NULL
l    -200
w    NULL
l    -200
l    NULL
l    NULL
w    NULL
w    200
*/

[叶子]

declare @t table (col varchar(1))
insert into @t
select 'w' union all
select 'w' union all
select 'l' union all
select 'w' union all
select 'l' union all
select 'w' union all
select 'l' union all
select 'l' union all
select 'l' union all
select 'w' union all
select 'w'

create table #t(rid int,col varchar(2),num int)
insert into #t(rid,col) 
select row_number() over (order by getdate()) as rid,* from @t

declare @i int set @i=1
update #t 
set num=@i,@i=case when col='l' then @i+1 else 0 end
select a.col,
case when isnull(b.col,'w')='w' and a.col='w' then 200
	 when b.col='w' and a.col='l' then -200 
	 when b.col='l' then 200*power(2,b.num) end as col2
from #t a left join #t b on a.rid=b.rid+1
drop table #t

/*
col  col2
---- -----------
w    200
w    200
l    -200
w    400
l    -200
w    400
l    -200
l    400
l    800
w    1600
w    200
*/

[叶子]

--又调整了一下，刚才有两个结果不对

declare @t table (col varchar(1))
insert into @t
select 'w' union all
select 'w' union all
select 'l' union all
select 'w' union all
select 'l' union all
select 'w' union all
select 'l' union all
select 'l' union all
select 'l' union all
select 'w' union all
select 'w'

create table #t(rid int,col varchar(2),num int)
insert into #t(rid,col) 
select row_number() over (order by getdate()) as rid,* from @t

declare @i int set @i=1
update #t 
set num=@i,@i=case when col='l' then @i+1 else 0 end
select a.col,
case when isnull(b.col,'w')='w' and a.col='w' then 200
     when b.col='l' and a.col='w' then 200*power(2,b.num)
     when a.col='l' then -200*power(2,b.num) end as col2
from #t a left join #t b on a.rid=b.rid+1
drop table #t

/*
col  col2
---- -----------
w    200
w    200
l    -200
w    400
l    -200
w    400
l    -200
l    -400
l    -800
w    1600
w    200
*/

[hhiew]

[Quote=引用 4 楼 maco_wang 的回复:]

SQL code
--又调整了一下，刚才有两个结果不对

declare @t table (col varchar(1))
insert into @t
select 'w' union all
select 'w' union all
select 'l' union all
select 'w' union all
select 'l' union all
select 'w' unio……
[/Quote]
非常有用，谢谢：）