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

Question

当然哪条记录在前面，父项的记录的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

Answer 1

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
endSelect * 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 行）

Answer 2

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 aSelect * from t1 order by fx,xhxh          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 行）

Answer 3

--用这个更新语句就行了.update 表 set item=(select sum(1) from 表 where 父项品编码=a.父项品编码 and 子项编码<=a.子项编码)
from 表 a