---这个改之前最好先备份,未经严格测试DECLARE @SERVICENO varchar(12)
declare @GDNO int
declare @LSNO int
set @GDNO=1
set @LSNO=1DECLARE cu_ChangeNo CURSOR FAST_FORWARD READ_ONLY FOR
select SERVICENO from t_client order by SERVICENOOPEN cu_ChangeNoFETCH NEXT FROM cu_ChangeNo INTO @SERVICENOWHILE @@FETCH_STATUS = 0
BEGIN
if @LSNO=30
BEGIN
set @GDNO= @GDNO+1
@LSNO=1
END
update t_client set SERVICENO=
right('000'+cast( @GDNO as varchar(4),4)+
right('0'+cast( @LSNO as varchar(2),2)
where SERVICENO=@SERVICENO
set @LSNO=@LSNO+1
FETCH NEXT FROM cu_ChangeNo INTO @SERVICENOENDCLOSE cu_ChangeNo
DEALLOCATE cu_ChangeNo
declare @GDNO int
declare @LSNO int
set @GDNO=1
set @LSNO=1DECLARE cu_ChangeNo CURSOR FAST_FORWARD READ_ONLY FOR
select SERVICENO from t_client order by SERVICENOOPEN cu_ChangeNoFETCH NEXT FROM cu_ChangeNo INTO @SERVICENOWHILE @@FETCH_STATUS = 0
BEGIN
if @LSNO=30
BEGIN
set @GDNO= @GDNO+1
@LSNO=1
END
update t_client set SERVICENO=
right('000'+cast( @GDNO as varchar(4),4)+
right('0'+cast( @LSNO as varchar(2),2)
where SERVICENO=@SERVICENO
set @LSNO=@LSNO+1
FETCH NEXT FROM cu_ChangeNo INTO @SERVICENOENDCLOSE cu_ChangeNo
DEALLOCATE cu_ChangeNo
解决方案 »
- 求高手教教我,如何把连接数据库的代码封装起来,然后在其他文件调用,,在线等待。。
- 怎样利用DTS将Excel文件导入到SQL Server数据库中(不是利用向导进行导入),
- 交叉报表时字段排序问题,急
- 恳请赐教一段SQL语句的简单问题
- 请教一个成绩统计问题?
- 如何获取与一个基本表相关联的所有视图?
- 如何把insert和update绑成一个事物?
- 获得时间差
- 我的电脑装SQl server 2008R2装不了,我机子是64位的。。
- win2003不支持sql2000 sp2 and below 怎么办?
- 如何在SQL2000 中实现在结果集中加一个表示排名的列 下面是我的SQL
- C# 按F11不能调试
declare @t_client table (SERVICENO varchar(20))
declare @i int
set @i=1
while @i<100
begin
insert @t_client select '20081118'+right('0000'+cast(@i as varchar(4)) ,4)
set @i=@i+1
end
--排序临时表
declare @t table (SERVICENO varchar(50),新编号 varchar(20))
insert @t
select SERVICENO,'' from @t_client order by SERVICENO
--新编号
declare @归档 varchar(4),@流水号 varchar(2),@新编号 varchar(20)
select @归档='0001',@流水号='01'update @t set @新编号=@归档+@流水号,
@流水号=case when @流水号='30' then '01' else
right('00'+cast(cast(@流水号 as int)+1 as varchar(4)),2) end,
@归档=case when @流水号='01' then right('0000'+cast(cast(@归档 as int)+1 as varchar(10)),4)
else @归档 end,
新编号=@新编号
--用临时表更新t_client表
select a.SERVICENO,b.新编号 from @t_client a
inner join @t b on a.SERVICENO=b.SERVICENO
/*结果
SERVICENO 新编号
-------------------- --------------------
200811180001 000101
200811180002 000102
...........................
200811180030 000130
200811180031 000201
...........................
200811180098 000408
200811180099 000409
*/