楼主的原始数据不对吧?
倒数第4行
c1 21005 13
而结果中又多出个b2来.drop table 表
GO
create table 表(ID varchar(10),ID_SUBNO varchar(20),Class_no varchar(10))
GO
insert into 表
select 'a1','432001','13'
union all select 'b1','432002','21'
union all select 'c1','23001','12'
union all select 'c2','31002','01'
union all select 'c3','32005','12'
union all select 'c4','23005','01'
union all select 'c5','21003','13'
union all select 'b2','21005','13'
union all select 'c7','230007','21'
union all select 'b4','330002','12'
union all select 'a2','330004','13'--更新
update a
set ID_SUBNO=Class_no+(select '000'+right(rtrim(count(*)+1),3) from 表 b where a.Class_no=b.Class_no and a.ID>b.ID)
from 表 a--结果
select * from 表ID ID_SUBNO Class_no
---------- -------------------- ----------
a1 130001 13
b1 210001 21
c1 120002 12
c2 010001 01
c3 120003 12
c4 010002 01
c5 130004 13
b2 130003 13
c7 210002 21
b4 120001 12
a2 130002 13(所影响的行数为 11 行)
倒数第4行
c1 21005 13
而结果中又多出个b2来.drop table 表
GO
create table 表(ID varchar(10),ID_SUBNO varchar(20),Class_no varchar(10))
GO
insert into 表
select 'a1','432001','13'
union all select 'b1','432002','21'
union all select 'c1','23001','12'
union all select 'c2','31002','01'
union all select 'c3','32005','12'
union all select 'c4','23005','01'
union all select 'c5','21003','13'
union all select 'b2','21005','13'
union all select 'c7','230007','21'
union all select 'b4','330002','12'
union all select 'a2','330004','13'--更新
update a
set ID_SUBNO=Class_no+(select '000'+right(rtrim(count(*)+1),3) from 表 b where a.Class_no=b.Class_no and a.ID>b.ID)
from 表 a--结果
select * from 表ID ID_SUBNO Class_no
---------- -------------------- ----------
a1 130001 13
b1 210001 21
c1 120002 12
c2 010001 01
c3 120003 12
c4 010002 01
c5 130004 13
b2 130003 13
c7 210002 21
b4 120001 12
a2 130002 13(所影响的行数为 11 行)
解决方案 »
- 您在 ADD_LOCAL 属性中指定的组件已安装。若要升级现有组件,请引用 template.ini 并将 UPGRADE 属性设置为该组件的名称。
- Sqlserver存储过程 executeUpdate方法不成功
- 求一个简单快捷的INSERT+UPDATE结合
- 奇怪的SQL远程连接问题。望达人指点。
- 如何获得数据库服务器的系统时间,急!!!
- window 7 更新数据库从05到08问题
- sql server 2000 不能在 windows 2003 上面正常安装,且安装之后无法远程访问数据库
- 如何创建索引?加分加分!
- 自编一基于数据库的软件(按DAO方式),给用户试用后,出现了有的人可以正常使用,而有的人在开启程序的时候系统就提示“无法对DAO/JET D
- SQL Server 2000 中如何为自增编号列重新编号?
- 重复记录处理问题
- 请高手帮忙一下子
goinsert into test(id,ID_Subno,Class_no)
select 'a1' , '432001' , '13'
union all select
'b1', '432002', '21'
union all select
'c1' , '23001' , '12'
union all select
'c2' , '31002' , '01'
union all select
'c3' , '32005' , '12'
union all select
'c4' , '23005' , '01'
union all select
'c5' , '21003' , '13'
union all select
'c1' , '21005' ,'13'
union all select
'c7' , '230007' ,'21'
union all select
'b4' ,'330002' ,'12'
union all select
'a2' ,'330004' ,'13'go
update test set id_subno=rtrim(a.class_no)+right('000'+cast((select count(1) from test where class_no=a.class_no and id<=a.id) as varchar),3)
from test aselect * from testdrop table test
goselect id,id_subno,class_no ,要更新列值=rtrim(a.class_no)+right('000'+cast((select count(1) from test where class_no=a.class_no and id<=a.id) as varchar),3)
from test a
order by class_no,id