;with t(select tel,row_number() over(order by newid()) as rowid from TEL_Info), t1 as( select id,t.tel,times from t inner join TEL_Info on t.rowid=TEL_Info.id) update u set u.tel=t1.tel from t1 inner join USER_Info u on t.id=u.id
;with t(select tel,row_number() over(order by newid()) as rowid from TEL_Info), t1 as( select id,t.tel,times from t inner join TEL_Info on t.rowid=TEL_Info.id) update u set u.tel=t1.tel from t1 inner join USER_Info u on t.id=u.id%6
CREATE PROCEDURE SP_GetRandomTable as Begin create table #tmp ( id int identity(1,1) not null, tel varchar(50) null )insert into #tmp(tel) select tel from dbo.TEL_Info order by NEWID() declare @userCount int select @userCount = count(*) from User_Infoselect a.id,b.tel,a.times,c.UserName from dbo.TEL_Info a,#tmp b,User_Info c where a.id = b.id and (c.id = (case a.id%@userCount when 0 then @userCount else a.id%@userCount end) )End 这个应该可以
from TEL_Info),
t1 as(
select id,t.tel,times
from t inner join TEL_Info on t.rowid=TEL_Info.id)
update u
set u.tel=t1.tel
from t1 inner join USER_Info u on t.id=u.id
from TEL_Info),
t1 as(
select id,t.tel,times
from t inner join TEL_Info on t.rowid=TEL_Info.id)
update u
set u.tel=t1.tel
from t1 inner join USER_Info u on t.id=u.id%6
as
Begin
create table #tmp
(
id int identity(1,1) not null,
tel varchar(50) null
)insert into #tmp(tel) select tel from dbo.TEL_Info order by NEWID()
declare @userCount int
select @userCount = count(*) from User_Infoselect a.id,b.tel,a.times,c.UserName from dbo.TEL_Info a,#tmp b,User_Info c
where a.id = b.id and (c.id = (case a.id%@userCount when 0 then @userCount else a.id%@userCount end) )End
这个应该可以