代码改变世界

批量随机修改悬赏问题发布人

  音乐让我说  阅读(276)  评论(0编辑  收藏  举报

最新我们的新网址 http://www.6eat.com 快要上线了,其中我们有一个版块叫“问答悬赏”,主要是网友提出问题,然后其他网友回答。我们程序员插入了很多测试数据,都是以“admin”的身份发布的,现在马上要上线了,需要把问题发布人改为我们公司N个文员的帐号,要不能是同一个帐号,现在要随机,我写了一段SQL脚本来解决,代码如下:

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
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

 

谢谢大家,请多多指教。

点击右上角即可分享
微信分享提示