算了还是建个数字表。 用row_number() over (order by newid())赋值好了,总好过一行一行更新
update tb set name='dd' where id in(select id from tb where id in(3,4,5)order by newid())
select *,bh=num from (select *,bh2=row_number() over (order by [id]) from web_enemy a with(nolock) where a.id in(1,2,3,4))b left join (select row_number() over (order by newid()) as bh2,[num] from web_num c with(nolock))d on b.bh2=d.bh2 order by d.num
update top (6) tb set number= (select top (1) N from (select 3 as n union select 4 union select 5 union select 6 union select 8 union select 10)k order by CHECKSUM(NEWID()))
T-MAC,这样UPDATE TOP 6能排序吗?怎么保证是更新的ID为1到6的数据
不。。 物理排序的 update tb set number= (select top (1) N from (select 3 as n union select 4 union select 5 union select 6 union select 8 union select 10)k order by CHECKSUM(NEWID())) where ID BETWEEN 1 AND 6
用row_number() over (order by newid())赋值好了,总好过一行一行更新
(select *,bh2=row_number() over (order by [id]) from web_enemy a with(nolock) where a.id in(1,2,3,4))b
left join
(select row_number() over (order by newid()) as bh2,[num] from web_num c with(nolock))d
on b.bh2=d.bh2 order by d.num
set number=
(select top (1) N from
(select 3 as n
union select 4
union select 5
union select 6
union select 8
union select 10)k
order by CHECKSUM(NEWID()))
物理排序的
update tb
set number=
(select top (1) N from
(select 3 as n
union select 4
union select 5
union select 6
union select 8
union select 10)k
order by CHECKSUM(NEWID()))
where ID BETWEEN 1 AND 6