泰祜:sql 如何把客户按类别平均随机分配
来源:百度文库 编辑:查人人中国名人网 时间:2024/10/06 20:52:08
理想范例:
c_id c_name c_bank
1 一 A
2 二 B
3 三 C
4 四 A
5 五 B
6 六 C
7 七 A
8 八 B
9 九 C
结果:
c_id c_name c_bank c_worker
1 一 A 1
2 二 B 3
3 三 C 2
4 四 A 2
5 五 B 2
6 六 C 1
7 七 A 3
8 八 B 1
9 九 C 3
用游标实现,假设你的表名为c,代码如下:
declare @c_id bigint,@r float
declare C_Cursor cursor for select c_id from c order by c_bank /*定义游标*/
open C_Cursor /*打开游标*/
fetch next from C_Cursor into @c_id /*读ID*/
while @@fetch_status=0 /*遍历数据库*/
begin
set @r=rand()
if @r<0.33333333
begin
update c set c_worker=1 where c_id=@c_id
fetch next from C_Cursor into @c_id /*读ID*/
set @r=rand()
if @r<0.33333333
if rand()<0.5
begin
update c set c_worker=2 where c_id=@c_id
fetch next from C_Cursor into @c_id /*读ID*/
update c set c_worker=3 where c_id=@c_id
fetch next from C_Cursor into @c_id /*读ID*/
end
else
begin
update c set c_worker=3 where c_id=@c_id
fetch next from C_Cursor into @c_id /*读ID*/
update c set c_worker=2 where c_id=@c_id
fetch next from C_Cursor into @c_id /*读ID*/
end
end
else if @r<0.66666666
begin
update c set c_worker=2 where c_id=@c_id
fetch next from C_Cursor into @c_id /*读ID*/
if rand()<0.5
begin
update c set c_worker=1 where c_id=@c_id
fetch next from C_Cursor into @c_id /*读ID*/
update c set c_worker=3 where c_id=@c_id
fetch next from C_Cursor into @c_id /*读ID*/
end
else
begin
update c set c_worker=3 where c_id=@c_id
fetch next from C_Cursor into @c_id /*读ID*/
update c set c_worker=1 where c_id=@c_id
fetch next from C_Cursor into @c_id /*读ID*/
end
end
else
begin
update c set c_worker=3 where c_id=@c_id
fetch next from C_Cursor into @c_id /*读ID*/
if rand()<0.5
begin
update c set c_worker=1 where c_id=@c_id
fetch next from C_Cursor into @c_id /*读ID*/
update c set c_worker=2 where c_id=@c_id
fetch next from C_Cursor into @c_id /*读ID*/
end
else
begin
update c set c_worker=2 where c_id=@c_id
fetch next from C_Cursor into @c_id /*读ID*/
update c set c_worker=1 where c_id=@c_id
fetch next from C_Cursor into @c_id /*读ID*/
end
end
end
close C_Cursor /*关闭游标*/
deallocate C_Cursor /*释放游标*/