怎么批量更新

如何批量更新
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
--------------------------------------------
IDbillidaid
110001
210002
310003
410011
510012
610013
710014
810015
这个很方便而且灵活，希望对你有所帮助：）