就是将表中某个字段更新为随机(从另外一个表获得)的数字
如表 AAid aa
1 0
2 0
3 0表BBid bb
1 1
2 2
3 3
4 4
5 5
6 6
7 7更新后
表A变成
Aid aa
1 4
2 1
3 5这个样子的,表A表B的数据大概都有100多条
怎样写SQL效率最高?
如表 AAid aa
1 0
2 0
3 0表BBid bb
1 1
2 2
3 3
4 4
5 5
6 6
7 7更新后
表A变成
Aid aa
1 4
2 1
3 5这个样子的,表A表B的数据大概都有100多条
怎样写SQL效率最高?
if object_id('[A]') is not null drop table [A]
create table [A] (Aid int,aa int)
insert into [A]
select 1,0 union all
select 2,0 union all
select 3,0
--> 测试数据: [B]
if object_id('[B]') is not null drop table [B]
create table [B] (Bid int,bb int)
insert into [B]
select 1,1 union all
select 2,2 union all
select 3,3 union all
select 4,4 union all
select 5,5 union all
select 6,6 union all
select 7,7update A Set AA=B.BB
from A
join(select row_number() over(order by newid()) id ,BB from B ) B on A.Aid=B.IDselect * from a
create table b(bid int, bb int);insert into a(aid, aa)
select
1,0 union all select
2,0 union all select
3,0;insert into b(bid,bb)
select
1,1 union all select
2,2 union all select
3,3 union all select
4,4 union all select
5,5 union all select
6,6 union all select
7,7;update a
set a.aa = b1.bb
from a inner join (select row_number() over(order by newid()) as bid, bb from b ) b1
on a.aid=b1.bid;select * from a;
if object_id('[A]') is not null drop table [A]
create table [A] (Aid int,aa int)
insert into [A]
select 1,0 union all
select 2,0 union all
select 3,0
--> 测试数据: [B]
if object_id('[B]') is not null drop table [B]
create table [B] (Bid int,bb int)
insert into [B]
select 1,1 union all
select 2,2 union all
select 3,3 union all
select 4,4 union all
select 5,5 union all
select 6,6 union all
select 7,7update A Set AA=B.BB
from A
join(select row_number() over(order by newid()) id ,BB from B ) B on A.Aid=B.IDselect * from a
(3 行受影响)
Aid aa
----------- -----------
1 7
2 2
3 4(3 行受影响)--AA可重复
update A1 set A1.AA=B.BB from
A A1 cross apply(
select top 1 Aid,B.BB from a,B where A.Aid=A1.AID
order by newid()
) Bselect * from A
(3 行受影响)
Aid aa
----------- -----------
1 2
2 2
3 5(3 行受影响)
if object_id('[A]') is not null drop table [A]
go
create table [A] (Aid int,aa int)
insert into [A]
select 1,0 union all
select 2,0 union all
select 3,0
--> 测试数据: [B]
if object_id('[B]') is not null drop table [B]
go
create table [B] (Bid int,bb int)
insert into [B]
select 1,1 union all
select 2,2 union all
select 3,3 union all
select 4,4 union all
select 5,5 union all
select 6,6 union all
select 7,7update A Set AA=(Select top 1 BB from B WHERE a.Aid<=b.Bid Order by newid())select * from a
/*
Aid aa
----------- -----------
1 7
2 6
3 5(3 行受影响)
*/1楼的这样改一下也没问题。
if object_id('[A]') is not null drop table [A]
create table [A] (Aid int,aa int)
insert into [A]
select 1,0 union all
select 2,0 union all
select 3,0
--> 测试数据: [B]
if object_id('[B]') is not null drop table [B]
create table [B] (Bid int,bb int)
insert into [B]
select 1,1 union all
select 2,2 union all
select 3,3 union all
select 4,4 union all
select 5,5 union all
select 6,6 union all
select 7,7--2000
if object_id('tempdb..#') is not null drop table #
go
create table #(id int identity ,bb int)
insert into #(bb) select bb from (select distinct bb from b) t order by newid()update a set aa=bb from a,# t where a.aid=t.idselect * from a
drop table #
drop table a
drop table b