declare @i int,@j varchar(20) set @i=0 set @j='12345678900987654321'update tab set field=substring(@j,@i,1), @i=case when @i+1>20 then 1 else @i+1 end update tab set field=10 where field=0
declare @ table (a varchar(100)) insert @ values('1000') insert @ values('1001') insert @ values('1002') insert @ values('1003') insert @ values('1004') insert @ values('1005') insert @ values('1006') insert @ values('1007') insert @ values('1008') insert @ values('1009') insert @ values('1010') insert @ values('1011') insert @ values('1012') insert @ values('1013') insert @ values('1014') insert @ values('1015') insert @ values('1016') insert @ values('1017') insert @ values('1018') insert @ values('1019') insert @ values('1020') insert @ values('1021') insert @ values('1022') insert @ values('1023') insert @ values('1024') insert @ values('1025') select *,0 值 into # from @declare @i int,@flag int,@num int,@flag1 int select @i=0,@flag=0,@num=0,@flag1=0update # set @num=case when @num<12 then @num+1 else 3 end,@flag1=case when @num=11 then 1 else 0 end,@flag=case when @num<12 then @flag else (@flag+1)%2 end,@i=case when @flag1=1 then @i when @flag1=0 and @flag=0 then @i+1 when @flag1=0 and @flag=1 then @i-1 end,值=@iselect * from # drop table #
改成一句: declare @i int,@j varchar(20) set @i=0 set @j='12345678900987654321'update tab set field=case substring(@j,@i,1)='0' then 10 else substring(@j,@i,1) end, @i=case when @i+1>20 then 1 else @i+1 end
J老师 这么高的招术也有? pfpf
分析啊: 唯一的条件:最好对ZIJIN按你要的排序建聚簇索引。不然记录量大了要出错。
即使你用字符串代替也可以啊。 id str 1 abc 2 sdgdfg 3 fgdfg ......然后用表连接。id=substring(.....)
set @i=0
set @j='12345678900987654321'update tab set
field=substring(@j,@i,1),
@i=case when @i+1>20 then 1 else @i+1 end
update tab set field=10 where field=0
insert @ values('1000')
insert @ values('1001')
insert @ values('1002')
insert @ values('1003')
insert @ values('1004')
insert @ values('1005')
insert @ values('1006')
insert @ values('1007')
insert @ values('1008')
insert @ values('1009')
insert @ values('1010')
insert @ values('1011')
insert @ values('1012')
insert @ values('1013')
insert @ values('1014')
insert @ values('1015')
insert @ values('1016')
insert @ values('1017')
insert @ values('1018')
insert @ values('1019')
insert @ values('1020')
insert @ values('1021')
insert @ values('1022')
insert @ values('1023')
insert @ values('1024')
insert @ values('1025')
select *,0 值 into # from @declare @i int,@flag int,@num int,@flag1 int
select @i=0,@flag=0,@num=0,@flag1=0update # set @num=case when @num<12 then @num+1 else 3 end,@flag1=case when @num=11 then 1 else 0 end,@flag=case when @num<12 then @flag else (@flag+1)%2 end,@i=case when @flag1=1 then @i when @flag1=0 and @flag=0 then @i+1 when @flag1=0 and @flag=1 then @i-1 end,值=@iselect * from #
drop table #
declare @i int,@j varchar(20)
set @i=0
set @j='12345678900987654321'update tab set
field=case substring(@j,@i,1)='0' then 10 else substring(@j,@i,1) end,
@i=case when @i+1>20 then 1 else @i+1 end
这么高的招术也有?
pfpf
唯一的条件:最好对ZIJIN按你要的排序建聚簇索引。不然记录量大了要出错。
id str
1 abc
2 sdgdfg
3 fgdfg
......然后用表连接。id=substring(.....)
我看你的语句和我的语句和索引没关系呀????