MSSQL2000表 单据中自动增加序号解决办法

MSSQL2000表 单据中自动增加序号
MSSQL2000表 单据中自动增加序号
表E_ORDERD和表E_ORDERDP (主从表结构)
条件E_ORDERD.BILLID=E_ORDERDP

表E_ORDERD
BILLID ,ITEMNO,QTY
1001, NULL, 123
1001, NULL, 1234
1001, NULL, 1212
1001, 2 , 30
1001, 3 , 40
1003, null , 40
1003, null , 40

表E_ORDERDP
BILLID ,ITEMNO,QTY
1001, NULL, 1
1001, NULL, 3
1001, NULL, 4
1001, 2 , 6
1001, 3 , 8
1003, 2 , 43
1003, null , 44


按照单据录入后按录入顺序更新ITEMNO
结果如下：
表E_ORDERD
BILLID ,ITEMNO,QTY
1001, 1, 123
1001, 2, 1234
1001, 3, 1212
1001, 4 , 30
1001, 5 , 40
1003, 1 , 40
1003, 2 , 40

表E_ORDERDP
BILLID ,ITEMNO,QTY
1001, 1, 1
1001, 2, 3
1001, 3, 4
1001, 4 , 6
1001, 5 , 8
1003, 1 , 43
1003, 2 , 44


[解决办法]

SQL code

用标识列方法--> --> (Roy)生成測試數據 if not object_id('Tempdb..#E_ORDERD') is null    drop table #E_ORDERDGoCreate table #E_ORDERD([BILLID] int,[ITEMNO] int,[QTY] int)Insert #E_ORDERDselect 1001,null,123 union allselect 1001,null,1234 union allselect 1001,null,1212 union allselect 1001,2,30 union allselect 1001,3,40 union allselect 1003,null,40 union allselect 1003,null,40Goalter table #E_ORDERD add ID int identitygoupdate aset [ITEMNO]=(select COUNT(1) from #E_ORDERD where [BILLID]=a.[BILLID] and ID<=a.ID)from #E_ORDERD as agoalter table #E_ORDERD drop column IDgoSelect * from #E_ORDERD/*BILLID    ITEMNO    QTY1001    1    1231001    2    12341001    3    12121001    4    301001    5    401003    1    401003    2    40*/