批量随机修改悬赏问题发布人
2010-08-14 19:49 音乐让我说 阅读(273) 评论(0) 编辑 收藏 举报最新我们的新网址 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
谢谢大家,请多多指教。
作者:音乐让我说(音乐让我说 - 博客园)
出处:http://music.cnblogs.com/
文章版权归本人所有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。