ID billid aid
1 1000
2 1000
3 1000
4 1001
5 1001
6 1001
7 1001
8 1001
根据billid更新aid
billid一致时 aid 从1递增1，也就是得到
ID billid aid
1 1000 1
2 1000 2
3 1000 3
4 1001 1
5 1001 2
6 1001 3
7 1001 4
8 1001 5
------解决方案--------------------
- SQL code
--> 测试数据：[tb]IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]GOCREATE TABLE [tb]([ID] INT,[billid] INT,[aid] int)INSERT [tb]SELECT 1,1000,NULL UNION ALLSELECT 2,1000,NULL UNION ALLSELECT 3,1000,NULL UNION ALLSELECT 4,1001,NULL UNION ALLSELECT 5,1001,NULL UNION ALLSELECT 6,1001,NULL UNION ALLSELECT 7,1001,NULL UNION ALLSELECT 8,1001,NULLGO--> 测试语句：;with t as(SELECT *,row_id=row_number() over(partition by billid order by id) FROM [tb])update t set aid=row_idselect * from tb/*ID billid aid----------- ----------- -----------1 1000 12 1000 23 1000 34 1001 15 1001 26 1001 37 1001 48 1001 5(8 行受影响)*/
------解决方案--------------------
- SQL code
--> 测试数据：[tb]IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]GOCREATE TABLE [tb]([ID] INT,[billid] INT,[aid] int)INSERT [tb]SELECT 1,1000,NULL UNION ALLSELECT 2,1000,NULL UNION ALLSELECT 3,1000,NULL UNION ALLSELECT 4,1001,NULL UNION ALLSELECT 5,1001,NULL UNION ALLSELECT 6,1001,NULL UNION ALLSELECT 7,1001,NULL UNION ALLSELECT 8,1001,NULLGO--> 2000：update t set t.aid=(select count(*) from tb where billid=t.billid and id<=t.id) from tb tselect * from tb/*ID billid aid----------- ----------- -----------1 1000 12 1000 23 1000 34 1001 15 1001 26 1001 37 1001 48 1001 5(8 行受影响)*/
------解决方案--------------------
用开窗函数
select ID billid,
row_number()over(partition by billid order by id) as aid
from table
--------------------------------------------
ID billid aid
1 1000 1
2 1000 2
3 1000 3
4 1001 1
5 1001 2
6 1001 3
7 1001 4
8 1001 5
这个很方便而且灵活，希望对你有所帮助：）