也有BOM的子项代码，有什么方法可以自动得出BOM的子项代码序号是项数？

[BarryW]

ITEM 父项品编码 子项编码

8810031060 6610030000
8810031060 6610033060

6610033060 5710123030
6610033060 5510033060

5510033060 5010033060
5510033060 3000290001
5510033060 3001760001
5510033060 3001200001

5010033060 4510033010
5010033060 1002710040
5010033060 3000400001
5010033060 3000120001
5010033060 3040310001
5010033060 2100090000

上面我有一个BOM子表父子项明细表，我如何才能自动可以实现它的子项代码序号是项数；

如：8810031060父项对应的子项有一条是6610030000，
8810031060 6610030000 在前面是第一条记录，在ITEM栏位下面就是“1”

8810031060父项对应的子项有一条是6610033060
8810031060 6610033060在前面是第二条记录，在ITEM栏位下面就是“2”

5510033060父项对应的子项有一条是5010033060
5510033060 5010033060在前面是第一条记录，在ITEM栏位下面就是“1”

5510033060父项对应的子项有一条是3000290001
5510033060 3000290001在前面是第二条记录，在ITEM栏位下面就是“2”

5510033060父项对应的子项有一条是3001760001
5510033060 3001760001在前面是第三条记录，在ITEM栏位下面就是“3”

5510033060父项对应的子项有一条是3001200001
5510033060 3001200001在前面是第四条记录，在ITEM栏位下面就是“4”
........
类推

[BarryW]

当然哪条记录在前面，父项的记录的ITEM哪条是1，2，3，4，都无所谓，并没有前后顺序之分，

5010033060 4510033010
5010033060 1002710040
5010033060 3000400001
5010033060 3000120001
5010033060 3040310001
5010033060 2100090000

如有6条，5010033060 4510033010 这一条的ITEM也可以是6

5010033060 2100090000这一条的ITEM也可以是1

[txlicenhe]

create table t1(xh int,fx varchar(20),zx varchar(20))
insert t1(fx,zx) Select '8810031060','6610030000'
union all select '8810031060','6610033060'
union all select '6610033060','5710123030'
union all select '6610033060','5510033060'
union all select '5510033060','5010033060'
union all select '5510033060','3000290001'
union all select '5510033060','3001760001'
union all select '5510033060','3001200001'
union all select '5010033060','4510033010'
union all select '5010033060','1002710040'
union all select '5010033060','3000400001'
union all select '5010033060','3000120001'
union all select '5010033060','3040310001'
union all select '5010033060','2100090000'

declare @i int
set @i = 0
update t1 set xh = 1 where fx not in (Select zx from t1)
while @@rowcount > 0
begin
set @i = @i+1
update t1 set xh = @i+1 where fx in (select zx from t1 where xh = @i) and IsNull(xh,0) = 0
end

Select * from t1
xh fx zx
----------- -------------------- --------------------
1 8810031060 6610030000
1 8810031060 6610033060
2 6610033060 5710123030
2 6610033060 5510033060
3 5510033060 5010033060
3 5510033060 3000290001
3 5510033060 3001760001
3 5510033060 3001200001
4 5010033060 4510033010
4 5010033060 1002710040
4 5010033060 3000400001
4 5010033060 3000120001
4 5010033060 3040310001
4 5010033060 2100090000

（所影响的行数为 14 行）

[txlicenhe]

create table t1(xh int,fx varchar(20),zx varchar(20))
insert t1(fx,zx) Select '8810031060','6610030000'
union all select '8810031060','6610033060'

union all select '6610033060','5710123030'
union all select '6610033060','5510033060'

union all select '5510033060','5010033060'
union all select '5510033060','3000290001'
union all select '5510033060','3001760001'
union all select '5510033060','3001200001'
union all select '5010033060','4510033010'
union all select '5010033060','1002710040'
union all select '5010033060','3000400001'
union all select '5010033060','3000120001'
union all select '5010033060','3040310001'
union all select '5010033060','2100090000'

update t1 set xh = (Select sum(1) from t1 where fx = a.fx and zx<= a.zx)
from t1 a

Select * from t1 order by fx,xh

xh fx zx
----------- -------------------- --------------------
1 5010033060 1002710040
2 5010033060 2100090000
3 5010033060 3000120001
4 5010033060 3000400001
5 5010033060 3040310001
6 5010033060 4510033010
1 5510033060 3000290001
2 5510033060 3001200001
3 5510033060 3001760001
4 5510033060 5010033060
1 6610033060 5510033060
2 6610033060 5710123030
1 8810031060 6610030000
2 8810031060 6610033060

（所影响的行数为 14 行）

[zjcxc]

--用这个更新语句就行了.

update 表 set item=(select sum(1) from 表 where 父项品编码=a.父项品编码 and 子项编码<=a.子项编码)
from 表 a