帮忙解决一下这个难题???

[loen113]

有此表
单号 月份 标号
sj123 200801 null
sj123 200802 null
sj123 200803 null
sj123 200804 8
sj123 200805 null
sj123 200806 null
sj110 200801 null
sj110 200802 null
sj110 200803 null
sj130 200801 null
sj130 200802 null
sj130 200803 6
sj130 200804 null
sj130 200805 null
sj130 200806 null

... .... ...



想得到以下
单号 月份 标号
sj123 200801 0
sj123 200802 1
sj123 200803 2
sj123 200804 8
sj123 200805 0
sj123 200806 1
sj110 200801 0
sj110 200802 1
sj110 200803 2
sj130 200801 0
sj130 200802 1
sj130 200803 6
sj130 200804 0
sj130 200805 1
sj130 200806 2


标号是从最小月份开始为0,下月累加,但是要将遇到不为空的记录月份的下月又从0开始标识

[loen113]

用游标的话,太慢了,能不能找到更好的方法

[zheninchangjiang]

--try
declare @i int
set @i=0
update tablename set bh=case when bh is not null then @i else bh end,@i=case when bh>0 then @i+1 else 0 end

[fcuandy]

DECLARE @t TABLE(f1 VARCHAR(10),f2 VARCHAR(6),f3 INT)
INSERT @t SELECT 'sj123',  200801,  null
UNION ALL SELECT 'sj123',  200802 , null
UNION ALL SELECT 'sj123',  200803  ,null
UNION ALL SELECT 'sj123',  200804,  8
UNION ALL SELECT 'sj123',  200805 , null
UNION ALL SELECT 'sj123',  200806  ,null
UNION ALL SELECT 'sj110',  200801,  null
UNION ALL SELECT 'sj110',  200802 , null
UNION ALL SELECT 'sj110',  200803  ,null
UNION ALL SELECT 'sj130',  200801,  null
UNION ALL SELECT 'sj130',  200802 , null
UNION ALL SELECT 'sj130',  200803  ,6
UNION ALL SELECT 'sj130',  200804,  null
UNION ALL SELECT 'sj130',  200805 , null
UNION ALL SELECT 'sj130',  200806  ,null 

SELECT f1,f2,ISNULL(f3,DENSE_RANK() OVER (PARTITION BY f1,gid ORDER BY f2)-1) FROM 
	(SELECT *,(SELECT ISNULL(MIN(f3),0) FROM @t WHERE f1=a.f1 AND f3 IS NOT NULL AND f2>=a.f2) gid FROM @t a) x
	ORDER BY CHARINDEX(f1,'sj123sj110sj130'),f2

[-狙击手-]

------------------------------------
-- Author: happyflystone  
-- Version:V1.001  
-- Date:2008-09-24 21:29:44
------------------------------------

-- Test Data: ta
If object_id('ta') is not null 
    Drop table ta
Go
Create table ta(单号 nvarchar(5),月份 varchar(6),标号 nvarchar(4))
Go
Insert into ta
 select 'sj123','200801',null union all
 select 'sj123','200802',null union all
 select 'sj123','200803',null union all
 select 'sj123','200804','8' union all
 select 'sj123','200805',null union all
 select 'sj123','200806',null union all
 select 'sj110','200801',null union all
 select 'sj110','200802',null union all
 select 'sj110','200803',null union all
 select 'sj130','200801',null union all
 select 'sj130','200802',null union all
 select 'sj130','200803','6' union all
 select 'sj130','200804',null union all
 select 'sj130','200805',null union all
 select 'sj130','200806',null 
Go
--Start

update a
set 标号 = power(2,cast(right(月份,2) as int) - 2 - 
isnull(cast(right((select top 1 月份 
            from ta 
            where 单号 = a.单号 and 月份 < a.月份 and 标号 is not null 
           ),2
     ) as int),0))
from ta a
where 标号 is null


Select * from ta


--Result:
/*
单号    月份     标号   
----- ------ ---- 
sj123 200801 0
sj123 200802 1
sj123 200803 2
sj123 200804 8
sj123 200805 0
sj123 200806 1
sj110 200801 0
sj110 200802 1
sj110 200803 2
sj130 200801 0
sj130 200802 1
sj130 200803 6
sj130 200804 0
sj130 200805 1
sj130 200806 2

（所影响的行数为 15 行）

*/
--End 

[等不到来世]

create table tb(dh varchar(10) , yf varchar(10),bh int)
go
insert tb select 'sj123','200801',null union select 'sj123','200802',null  union select 'sj123','200803',null 
union select 'sj123','200804',8 union select 'sj123','200805',null union select 'sj123','200806',null
union select 'sj110','200801',null union select 'sj110','200802',null union select 'sj110','200803',null
union select 'sj130','200801',null union select 'sj130','200802',null union select 'sj130','200803',6
union select 'sj130','200804',null union select 'sj130','200805',null union select 'sj130','200806',null
go
select dh,yf,bh=isnull(bh,(select count(*) from tb where dh=a.dh and yf<a.yf and yf>(select isnull(max(yf),'190001') from tb where dh=a.dh and yf<a.yf and bh is not null)))
from tb a

/*
dh		yf		bh
---------------------
sj110	200801	0
sj110	200802	1
sj110	200803	2
sj123	200801	0
sj123	200802	1
sj123	200803	2
sj123	200804	8
sj123	200805	0
sj123	200806	1
sj130	200801	0
sj130	200802	1
sj130	200803	6
sj130	200804	0
sj130	200805	1
sj130	200806	2
*/

[loen113]

szx1999 的答案比较正确,不过还是有点小问题...不过给分了