select 列1,[列2]=case when 列2=0 then substring(列1,len(列1)-3,2) else 列2 end from 表名
--這樣?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, 0select col1, col2, col3=case when col2=0 then stuff(col1, len(col1), 1, '0') else col1 end from T--result col1 col2 col3 ----------- ----------- ----------- 330 1 330 331 0 330 332 0 330 340 2 340 341 0 340(5 row(s) affected)
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, 0select 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 substring(cast(列1 as varchar),len(列1)-2,2) else 列2 end from @T(5 行受影响) 列1 列3 ----------- ----------- 330 1 331 33 332 33 340 2 341 34(5 行受影响)
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 行受影响)
substring(cast(a.列1 as varchar),len(a.列1)-2,2)只取十位和百位
---最了解你的人是我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
笔误 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 where a.col2=0 and RIGHT(a.col1,1)<>0
from 表名
insert T select 330, 1
union all select 331, 0
union all select 332, 0
union all select 340, 2
union all select 341, 0select col1, col2,
col3=case when col2=0 then stuff(col1, len(col1), 1, '0') else col1 end
from T--result
col1 col2 col3
----------- ----------- -----------
330 1 330
331 0 330
332 0 330
340 2 340
341 0 340(5 row(s) affected)
insert T select 330, 1
union all select 331, 0
union all select 332, 0
union all select 340, 2
union all select 341, 0select 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)
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 substring(cast(列1 as varchar),len(列1)-2,2) else 列2 end
from @T(5 行受影响)
列1 列3
----------- -----------
330 1
331 33
332 33
340 2
341 34(5 行受影响)
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 行受影响)
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
/*
col1 col2
330 1
331 3
332 1
340 2
341 2
*/
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
where a.col2=0 and RIGHT(a.col1,1)<>0