34,590
社区成员
发帖
与我相关
我的任务
分享
use test
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
id int,
pid int,
mark int
)
go
--插入测试数据
insert into tb select 11,null,null
union all select 21,null,null
union all select 31,null,null
union all select 4,null,1
union all select 5,null,1
union all select 6,null,1
go
--代码实现
update t1 set pid=aid
from tb t1,( select aid=a.id,bid=b.id from (
select *,idd=row_number()over(order by getdate()) from tb where mark is null ) a,(
select *,idd=row_number()over(order by getdate()) from tb where mark is not null )b
where a.idd=b.idd )t2
where t1.id=t2.bid
select * from tb
/*测试结果
id pid mark
---------------------
11 NULL NULL
21 NULL NULL
31 NULL NULL
4 11 1
5 21 1
6 31 1
(6 行受影响)
*/
create table tb(id int,pid int,mark int)
insert into tb
select 11,null,null union all
select 21,null,null union all
select 31,null,null union all
select 4,null,1 union all
select 5,null,1 union all
select 6,null,1
go
update b
set b.pid = a.id
from tb a,tb b
where a.mark is null and b.mark is not null
and cast(left(ltrim(a.id),1) as int) + 3 = b.id
select * from tb
drop table tb
/*
id pid mark
----------- ----------- -----------
11 NULL NULL
21 NULL NULL
31 NULL NULL
4 11 1
5 21 1
6 31 1
(6 行受影响)
*/
--楼主应该再清楚一些。