求答案：数据分组显示，增加递增编号解决方法

求答案：数据分组显示，增加递增编号
数据如下：
NO 顺序
000000000001 1
000000000002 1
000000000003 1
010000000001 5
010000000001 4
010000000001 2
010000000001 1
010000000002 4
010000000002 3
010000000002 2
010000000002 1


想得到的结果：
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

有一点点思路，可是不能实现，请各位帮忙看看，谢谢。

[解决办法]

SQL code

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 2000select * , 编号 = (select count(1) from tb where NO = t.NO and 顺序 < t.顺序) + 1 from tb t--sql 2005select * , 编号 = 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)goinsert 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 goselect * ,num=identity(int,1,1) into #temp from  testgoselect no,顺序, (select count(1) from #temp where a.no=no and num<=a.num) 编号  from  #temp agodrop table #temp,testgo/*no                   顺序          编号          -------------------- ----------- ----------- 000000000001         1           1000000000002         1           1000000000003         1           1010000000001         5           1010000000001         4           2010000000001         2           3010000000001         1           4010000000002         4           1010000000002         3           2010000000002         2           3010000000002         1           4（所影响的行数为 11 行）*/