用标识列方法

--> --> (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
*/