求解实现分组按序递增修改字段的SQL语句

[goths]

假设有这样一个表
ID ID_SUBNO Class_no
a1 432001 13
b1 432002 21
c1 23001 12
c2 31002 01
c3 32005 12
c4 23005 01
c5 21003 13
c1 21005 13
c7 230007 21
b4 330002 12
a2 330004 13
... ...... .....
然后实现修改表里的数据为
ID ID_SUBNO Class_no
a1 13001 13
b1 21001 21
c1 12001 12
c2 01001 01
c3 12002 12
c4 01002 01
c5 13002 13
b2 13003 13
c7 21002 21
b4 12003 12
a2 13004 13
... ...... .....
最好按类别递增的顺序是
比较ID_SUBNO的大小顺序来增加的
小弟不才 只能实现单个分组 修改的也只是结果集里的数据 实际表没有实现 ，哪个老大哥给我解决语句。

[gahade]

楼主的原始数据不对吧?
倒数第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 行）

[ReViSion]

create table test (id varchar(10),ID_Subno varchar(10),class_no varchar(10))
go

insert 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 a

select * from test

drop table test


go

select 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

[ReViSion]

后面写多了点，哈哈