最新我们的新网址 http://www.6eat.com 快要上线了,其中我们有一个版块叫“问答悬赏”,主要是网友提出问题,然后其他网友回答。我们程序员插入了很多测试数据,都是以“admin”的身份发布的,现在马上要上线了,需要把问题发布人改为我们公司N个文员的帐号,要不能是同一个帐号,现在要随机,我写了一段SQL脚本来解决,代码如下:
declare @tempCount int
select @tempCount=count(*) from
(
select AskUser,count(Id) as AskTime from dbo.Eat_Reward_AskInfo group by AskUser having count(Id) > 4
) as MyTempTable
declare @i int
set @i=1
declare @tempUser int
declare @tempChildCount int
while(@i <= @tempCount)
begin
select @tempUser=AskUser from
(
select row_number() over(order by count(Id) Desc) as MyNewId, AskUser,count(Id) as AskTime from dbo.Eat_Reward_AskInfo group by AskUser having count(Id) > 4
) as MyTempTable where MyNewId=@i
select @tempChildCount=count(*) from Eat_Reward_AskInfo where AskUser=@tempUser
declare @j int
set @j = 1
declare @tempMyChildId int
while(@j <= @tempChildCount)
begin
select @tempMyChildId=Id from
(
select row_number() over(order by Id asc) as MyNewChildId,* from Eat_Reward_AskInfo where AskUser=@tempUser
) as MyTempChildTable where MyNewChildId=@j
update Eat_Reward_AskInfo set AskUser=
(
select top 1 Id from Eat_Users where UserName in ('flash3d','我来了','我是版主','骆驼草','水晶','新餐饮','温柔的一刀','简二先生','草叶','迷失在那年','义子','珍珠','stere','蓝冰月','半夏','点点雨','江城子','艾瑞克','六月的雨','幸运石','新时代莫愁','小婷子') order by newId()
) where Id=@tempMyChildId
set @j=@j + 1
end
set @i=@i + 1
end
谢谢大家,请多多指教。