22,206
社区成员
发帖
与我相关
我的任务
分享
drop table tableA
create table TableA
(Col01 varchar(10),Col02 varchar(10))
insert into TableA
select '张三','80' union all
select '张三','87' union all
select null,'ABC' union all
select '李四','32' union all
select '赵5','83' union all
select null,'kk' union all
select null,'TT'
;with t
as
(
select col01,
col02,
row_number() over(order by @@servername) as rownum
from tableA
)
update t
set col01 = case when t.col01 is not null
then t.col01
else (select top 1 col01
from t t1
where t1.rownum < t.rownum and t1.col01 is not null
order by t1.rownum desc)
end
select *
from tableA
/*
Col01 Col02
张三 80
张三 87
张三 ABC
李四 32
赵5 83
赵5 kk
赵5 TT
*/
--update就自己写吧
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 cte as(
select ROW_NUMBER()over(order by getdate()) rn,* from TableA
)
select Col01=case when isnull(Col01,'')=''
then (select max(Col01) from cte b where b.rn<a.rn and isnull(b.Col01,'')>'')
else Col01 end
,Col02 from cte a
--
Col01 Col02
---------- ----------
张三 80
张三 87
张三 ABC
李四 32
赵5 83
赵5 kk
赵5 TT
(7 行受影响)
drop table TableA
drop table tableA
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 = case when t.col01 <> ''
then t.col01
else (select top 1 col01
from t t1
where t1.rownum < t.rownum and t1.col01 <> ''
order by t1.rownum desc)
end
select *
from tableA
/*
Col01 Col02
张三 80
张三 87
张三 ABC
李四 32
赵5 83
赵5 kk
赵5 TT
*/
--忘记帖结果了
create table #tb(Col01 varchar(10),Col02 varchar(10))
insert into #tb
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 cte as(
select ROW_NUMBER()over(order by getdate()) rn,* from #tb
)
select case when isnull(Col01,'')=''
then (select top 1 Col01 from cte b where b.rn<a.rn and b.Col01<>'' Order by rn desc)
else Col01 end as Col02,Col02
from cte a
/*
Col02 Col02
张三 80
张三 87
张三 ABC
李四 32
赵5 83
赵5 kk
赵5 TT
*/
create table #tb(Col01 varchar(10),Col02 varchar(10))
insert into #tb
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 cte as(
select ROW_NUMBER()over(order by getdate()) rn,* from #tb
)
select case when isnull(Col01,'')='' then (select top 1 Col01 from cte b where b.rn<a.rn and b.Col01<>'' Order by rn desc) else Col01 end,Col02
from cte a
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 (select 0)) '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
/*
Col01 Col02
张三 80
赵5 87
张三 ABC
赵5 32
张三 83
赵5 kk
张三 TT
*/
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 cte as(
select ROW_NUMBER()over(order by getdate()) rn,* from TableA
)
select case when isnull(Col01,'')='' then (select Col01 from cte b where b.rn+2=a.rn) else Col01 end,Col02 from cte a
drop table TableA
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 (select 0)) '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)
*/
[/quote]
产生顺序号这个明白。我的意思是讲你第一个代码用getdate()产生顺序号,貌似有点儿不妥当。
selec 0 就感觉好多了
因为可能钻牛角尖了
多谢斑斑回复了