急问，解决马上揭帖
数据库里如下记录

330         1      
331         0
332         0
340         2
341         0

如果列2为0，则将0变为列1百位（33，34）个位为0的列2的值
上例中，前三行列2都为1，后两行列2都为2

------解决方案--------------------
create table T(col1 int, col2 int)
insert T select 330, 1
union all select 331, 0
union all select 332, 0
union all select 340, 2
union all select 341, 0

select col1, col2,
col3=case when col2=0 then (select top 1 col2 from T where col2 <> 0 and col1 <tmp.col1 order by col1 desc) else col2 end
from T as tmp

--result
col1 col2 col3
----------- ----------- -----------
330 1 1
331 0 1
332 0 1
340 2 2
341 0 2

(5 row(s) affected)

------解决方案--------------------
declare @T table (列1 int, 列2 int)
insert @T select 330, 1
union all select 331, 0
union all select 332, 0
union all select 340, 2
union all select 341, 0
select 列1,
[列3]=case when 列2=0
then (select min(列2) from @t where substring(cast(列1 as varchar),len(列1)-2,2)
=substring(cast(a.列1 as varchar),len(a.列1)-2,2) and 列2> 0) else 列2 end

from @T a

(5 行受影响)
列1 列3
----------- -----------
330 1
331 1
332 1
340 2
341 2

(5 行受影响)


------解决方案--------------------
---最了解你的人是我

create table t1(col1 varchar(10),col2 int)
insert t1 select '330 ', 1
union all select '331 ', 3
union all select '332 ', 0
union all select '340 ', 2
union all select '341 ', 0


update a
set col2=(select top 1 col2 from t1 b where substring(b.col1,1,len(b.col1)-1)
= substring(a.col1,1,len(a.col1)-1)order by col1)
from t1 a ,t1 b
where a.col2=0 and RIGHT(a.col1,1) <> 0