MSSQL2000表单据中自动增加序号解决办法-MSSQL教程-爱易网页

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

日期：2014-05-18　浏览次数：20902 次

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_ORDERD
Go
Create table #E_ORDERD([BILLID] int,[ITEMNO] int,[QTY] int)
Insert #E_ORDERD
select 1001,null,123 union all
select 1001,null,1234 union all
select 1001,null,1212 union all
select 1001,2,30 union all
select 1001,3,40 union all
select 1003,null,40 union all
select 1003,null,40
Go
alter table #E_ORDERD add ID int identity
go
update a
set [ITEMNO]=(select COUNT(1) from #E_ORDERD where [BILLID]=a.[BILLID] and ID<=a.ID)
from #E_ORDERD as a
go
alter table #E_ORDERD drop column ID
go
Select * from #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
*/