SELECT *,编号=(SELECT COUNT(*) FROM TB WHERE NO=T.NO AND 顺序<=T.顺序) FROM TB T

------解决方案--------------------
SQL code
select *,编号=row_number over(partition by NO order by 顺序) from tb

------解决方案--------------------

SQL code
--sql 2000
select * , 编号 = (select count(1) from tb where NO = t.NO and 顺序 < t.顺序) + 1 from tb t

--sql 2005
select * , 编号 = row_number() over(partition by NO order by 顺序) from tb

------解决方案--------------------

探讨

SQL codeSELECT *,编号=(SELECT COUNT(*) FROM TB WHERE NO=T.NO AND 顺序<=T.顺序) FROM TB T 


------解决方案--------------------

SQL code
 
create table test(NO varchar(20),顺序 int)
go
insert test select '000000000001' ,       1 
insert test select '000000000002' ,       1 
insert test select '000000000003' ,       1 
insert test select '010000000001' ,       5 
insert test select '010000000001' ,       4 
insert test select '010000000001' ,       2 
insert test select '010000000001' ,       1 
insert test select '010000000002' ,       4 
insert test select '010000000002' ,       3 
insert test select '010000000002' ,       2 
insert test select '010000000002' ,       1 
go
select * ,num=identity(int,1,1) into #temp from  test
go
select no,顺序, (select count(1) from #temp where a.no=no and num<=a.num) 编号  from  #temp a
go
drop table #temp,test
go
/*

no                   顺序          编号          
-------------------- ----------- ----------- 
000000000001         1           1
000000000002         1           1
000000000003         1           1
010000000001         5           1
010000000001         4           2
010000000001         2           3
010000000001         1           4
010000000002         4           1
010000000002         3           2
010000000002         2           3
010000000002         1           4

（所影响的行数为 11 行）
*/