~~~~~~~~~SQL更新语句，马上给分！~~~~~~~
TableA
Col01 Col02
张三 80
张三 87
ABC
李四 32
赵5 83
kk
TT
希望得到结果如下：
Col01 Col02
张三 80
张三 87
张三 ABC
李四 32
赵5 83
赵5 kk
赵5 dadfa
[解决办法]
create table TableA
(Col01 varchar(10),Col02 varchar(10))
insert into TableA
select '张三','80' union all
select '张三','87' union all
select '','ABC' union all
select '李四','32' union all
select '赵5','83' union all
select '','kk' union all
select '','TT'
-- 更新
with t as
(select Col01,Col02,
row_number() over(order by getdate()) 'rn'
from TableA)
update a
set a.Col01=(select top 1 b.Col01 from t b
where b.rn<a.rn and b.Col01<>''
order by b.rn desc)
from t a
where a.Col01=''
-- 结果
select Col01,Col02 from TableA
/*
Col01 Col02
---------- ----------
张三 80
张三 87
张三 ABC
李四 32
赵5 83
赵5 kk
赵5 TT
(7 row(s) affected)
*/
create table TableA
(Col01 varchar(10),Col02 varchar(10))
insert into TableA
select '张三','80' union all
select '张三','87' union all
select '','ABC' union all
select '李四','32' union all
select '赵5','83' union all
select '','kk' union all
select '','TT'
;with t
as
(
select col01,
col02,
row_number() over(order by @@servername) as rownum
from tableA
)
update t
set col01 = t2.col01
from t t1
inner join t t2
on t1.rownum > t2.rownum + 1
select *
from tableA
/*
Col01Col02
张三80
赵587
张三ABC
赵532
张三83
赵5kk
张三TT
*/
