--> 测试数据: #T if object_id('tempdb.dbo.#T') is not null drop table #T create table #T (id int identity, C1 int,C2 int) insert into #T select 1,null union all select 1,null union all select 1,123 union all select 2,null union all select 2,null union all select 2,234update #T set C2=case when C2 is null then (select top 1 C2 from #T where ID >= a.ID and C2 is not null order by ID) else C2 end from #T aselect * from #T/* id C1 C2 ----------- ----------- ----------- 1 1 123 2 1 123 3 1 123 4 2 234 5 2 234 6 2 234 */
create table a ( id1 int, id2 int ) insert into a select 1,null union all select 1,null union all select 1,123 union all select 2,null union all select 2,null union all select 2,234 --try update b set b.id2 = c.id2 from (select * from a where id2 is null) b inner join (select * from a where id2 is not null) c on c.id1 = b.id1 id1 id2 ----------- ----------- 1 123 1 123 1 123 2 234 2 234 2 234(6 row(s) affected)
哦,8楼的是OK的,忘记还有个分组的ID1/C1了。
--> 测试数据
declare @tb table ([id] int,[qty] nvarchar(4)) Insert into @tb select 1,null union all select 1,null union all select 1,123 union all select 2,null union all select 2,null union all select 2,234
--Select * from @tb--select *,(select max(qty) from @tb where id =a.id) from @tb aupdate a set qty = (select max(qty) from @tb where id =a.id) from @tb aselect * from @tb /* id qty ----------- ---- 1 123 1 123 1 123 2 234 2 234 2 234(6 row(s) affected)*/
1 , null
1 ,123
2, null
2, null
2 , 234
更新为:
1 ,123
1 ,123
1 ,123
2, 234
2, 234
2 , 234
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (id int identity, C1 int,C2 int)
insert into #T
select 1,null union all
select 1,null union all
select 1,123 union all
select 2,null union all
select 2,null union all
select 2,234update #T set C2=case when C2 is null then (select top 1 C2 from #T where ID >= a.ID and C2 is not null order by ID) else C2 end from #T aselect * from #T/*
id C1 C2
----------- ----------- -----------
1 1 123
2 1 123
3 1 123
4 2 234
5 2 234
6 2 234
*/
(
id1 int,
id2 int
)
insert into a
select
1,null union all select
1,null union all select
1,123 union all select
2,null union all select
2,null union all select
2,234
--try
update b
set b.id2 = c.id2
from (select * from a where id2 is null) b
inner join (select * from a where id2 is not null) c on c.id1 = b.id1
id1 id2
----------- -----------
1 123
1 123
1 123
2 234
2 234
2 234(6 row(s) affected)
declare @tb table ([id] int,[qty] nvarchar(4))
Insert into @tb
select 1,null union all
select 1,null union all
select 1,123 union all
select 2,null union all
select 2,null union all
select 2,234
--Select * from @tb--select *,(select max(qty) from @tb where id =a.id) from @tb aupdate a
set qty = (select max(qty) from @tb where id =a.id)
from @tb aselect * from @tb
/*
id qty
----------- ----
1 123
1 123
1 123
2 234
2 234
2 234(6 row(s) affected)*/