以下为昨天的问题两子件相同的情况其实应该是类似这样的形式
表(TableA)
ID 子件编号 父件编号 父件ID
1 F001 NULL NULL
2 S001 S002 NULL
3 S001 F001 NULL
4 M001 S001 NULL
5 M002 S001 NULL
6 M001 S001 NULL
7 M002 S001 NULL
8 S002 F001 NULL
9 M001 S002 NULL
10 M003 S002 NULL 注:除ID外,很多数据重复
想通过一UPDATE更新语句达到下面的结果,不知是否可行,请高手帮忙
ID 子件编号 父件编号 父件ID
1 F001 NULL NULL
2 S001 S002 8
3 S001 F001 1
4 M001 S001 2
5 M002 S001 2
6 M001 S001 3
7 M002 S001 3
8 S002 F001 1
9 M001 S002 8
10 M003 S002 8 此TableA中,S001,M001,M002肯定是成对出现的. //非常感谢JiangHongTao及其他高手的热心帮忙
得到如下答案
declare @t table (id int,子件编号 varchar(10),父件编号 varchar(10),父件ID int)
insert @t select 1 , 'F001' , 'NULL' , NULL
insert @t select 2 , 'S001' , 'S002' , NULL
insert @t select 3 , 'S001' , 'F001' , NULL
insert @t select 4 , 'M001' , 'S001' , NULL
insert @t select 5 , 'M002' , 'S001' , NULL
insert @t select 6 , 'M001' , 'S001' , NULL
insert @t select 7 , 'M002' , 'S001' , NULL
insert @t select 8 , 'S002' , 'F001' , NULL
insert @t select 9 , 'M001' , 'S002' , NULL
insert @t select 10 , 'M003' , 'S002' , NULLupdate a set 父件ID = xh from @t a,(
select b.id,b.子件编号,父件编号,(select count(*) from @t where 子件编号 = b.子件编号 and id < b.id)%fzs+a.id xh
from (select 子件编号,min(id) id,count(*) fzs from @t group by 子件编号) a,@t b
where a.子件编号 = b.父件编号
) b where a.id = b.id
select * from @t
/*
id 子件编号 父件编号 父件ID
----------- ---------- ---------- -----------
1 F001 NULL NULL
2 S001 S002 8
3 S001 F001 1
4 M001 S001 2
5 M002 S001 2
6 M001 S001 3
7 M002 S001 3
8 S002 F001 1
9 M001 S002 8
10 M003 S002 8
*/今用以下测试数据测试,发现好像不太对
declare @t table (id int,子件编号 varchar(10),父件编号 varchar(10),父件ID int)
insert @t select 27 , 'ANC00100' , 'NULL' , NULL
insert @t select 28 , 'ANA00070' , 'ANC00250' , NULL
insert @t select 29 , 'ANA00080' , 'ANC00250' , NULL
insert @t select 30 , 'ANC00250' , 'ANC00010' , NULL
insert @t select 31 , 'ANC00010' , 'ANC00100' , NULL
insert @t select 32 , 'ANA00070' , 'ANC00250' , NULL
insert @t select 33 , 'ANA00080' , 'ANC00250' , NULL
insert @t select 34 , 'ANC00250' , 'ANC00100' , NULL
update a set 父件ID = xh from @t a,(
select b.id,b.子件编号,父件编号,(select count(*) from @t where 子件编号 = b.子件编号 and id < b.id)%fzs+a.id xh
from (select 子件编号,min(id) id,count(*) fzs from @t group by 子件编号) a,@t b
where a.子件编号 = b.父件编号
) b where a.id = b.idselect * from @t/*
id 子件编号 父件编号 父件ID
----------- ---------- ---------- -----------
27 ANC00100 NULL NULL
28 ANA00070 ANC00250 30
29 ANA00080 ANC00250 30
30 ANC00250 ANC00010 31
31 ANC00010 ANC00100 27
32 ANA00070 ANC00250 31 这里的父件ID似乎应该是34
33 ANA00080 ANC00250 31 这里的父件ID似乎应该是34
34 ANC00250 ANC00100 27
*/特请JiangHongTao和其他高手再帮忙看看
不过小弟懒,实在是这SQL写得太高深了,看不明白,再次感谢!
表(TableA)
ID 子件编号 父件编号 父件ID
1 F001 NULL NULL
2 S001 S002 NULL
3 S001 F001 NULL
4 M001 S001 NULL
5 M002 S001 NULL
6 M001 S001 NULL
7 M002 S001 NULL
8 S002 F001 NULL
9 M001 S002 NULL
10 M003 S002 NULL 注:除ID外,很多数据重复
想通过一UPDATE更新语句达到下面的结果,不知是否可行,请高手帮忙
ID 子件编号 父件编号 父件ID
1 F001 NULL NULL
2 S001 S002 8
3 S001 F001 1
4 M001 S001 2
5 M002 S001 2
6 M001 S001 3
7 M002 S001 3
8 S002 F001 1
9 M001 S002 8
10 M003 S002 8 此TableA中,S001,M001,M002肯定是成对出现的. //非常感谢JiangHongTao及其他高手的热心帮忙
得到如下答案
declare @t table (id int,子件编号 varchar(10),父件编号 varchar(10),父件ID int)
insert @t select 1 , 'F001' , 'NULL' , NULL
insert @t select 2 , 'S001' , 'S002' , NULL
insert @t select 3 , 'S001' , 'F001' , NULL
insert @t select 4 , 'M001' , 'S001' , NULL
insert @t select 5 , 'M002' , 'S001' , NULL
insert @t select 6 , 'M001' , 'S001' , NULL
insert @t select 7 , 'M002' , 'S001' , NULL
insert @t select 8 , 'S002' , 'F001' , NULL
insert @t select 9 , 'M001' , 'S002' , NULL
insert @t select 10 , 'M003' , 'S002' , NULLupdate a set 父件ID = xh from @t a,(
select b.id,b.子件编号,父件编号,(select count(*) from @t where 子件编号 = b.子件编号 and id < b.id)%fzs+a.id xh
from (select 子件编号,min(id) id,count(*) fzs from @t group by 子件编号) a,@t b
where a.子件编号 = b.父件编号
) b where a.id = b.id
select * from @t
/*
id 子件编号 父件编号 父件ID
----------- ---------- ---------- -----------
1 F001 NULL NULL
2 S001 S002 8
3 S001 F001 1
4 M001 S001 2
5 M002 S001 2
6 M001 S001 3
7 M002 S001 3
8 S002 F001 1
9 M001 S002 8
10 M003 S002 8
*/今用以下测试数据测试,发现好像不太对
declare @t table (id int,子件编号 varchar(10),父件编号 varchar(10),父件ID int)
insert @t select 27 , 'ANC00100' , 'NULL' , NULL
insert @t select 28 , 'ANA00070' , 'ANC00250' , NULL
insert @t select 29 , 'ANA00080' , 'ANC00250' , NULL
insert @t select 30 , 'ANC00250' , 'ANC00010' , NULL
insert @t select 31 , 'ANC00010' , 'ANC00100' , NULL
insert @t select 32 , 'ANA00070' , 'ANC00250' , NULL
insert @t select 33 , 'ANA00080' , 'ANC00250' , NULL
insert @t select 34 , 'ANC00250' , 'ANC00100' , NULL
update a set 父件ID = xh from @t a,(
select b.id,b.子件编号,父件编号,(select count(*) from @t where 子件编号 = b.子件编号 and id < b.id)%fzs+a.id xh
from (select 子件编号,min(id) id,count(*) fzs from @t group by 子件编号) a,@t b
where a.子件编号 = b.父件编号
) b where a.id = b.idselect * from @t/*
id 子件编号 父件编号 父件ID
----------- ---------- ---------- -----------
27 ANC00100 NULL NULL
28 ANA00070 ANC00250 30
29 ANA00080 ANC00250 30
30 ANC00250 ANC00010 31
31 ANC00010 ANC00100 27
32 ANA00070 ANC00250 31 这里的父件ID似乎应该是34
33 ANA00080 ANC00250 31 这里的父件ID似乎应该是34
34 ANC00250 ANC00100 27
*/特请JiangHongTao和其他高手再帮忙看看
不过小弟懒,实在是这SQL写得太高深了,看不明白,再次感谢!
insert @t select 27 , 'ANC00100' , 'NULL' , NULL
insert @t select 28 , 'ANA00070' , 'ANC00250' , NULL
insert @t select 29 , 'ANA00080' , 'ANC00250' , NULL
insert @t select 30 , 'ANC00250' , 'ANC00010' , NULL
insert @t select 31 , 'ANC00010' , 'ANC00100' , NULL
insert @t select 32 , 'ANA00070' , 'ANC00250' , NULL
insert @t select 33 , 'ANA00080' , 'ANC00250' , NULL
insert @t select 34 , 'ANC00250' , 'ANC00100' , NULL
/*update a set 父件ID = xh
from @t a,(
select b.id,b.子件编号,父件编号,(select count(*) from @t where 子件编号 = b.子件编号 and id < b.id)%fzs+a.id xh
from (select 子件编号,min(id) id,count(*) fzs from @t group by 子件编号) a,@t b
where a.子件编号 = b.父件编号
) b where a.id = b.id
*/select id,
子件编号,
父件编号,
父件ID = (select top 1 id from @t where 子件编号 = a.父件编号 order by id)
from @t a
/*
id 子件编号 父件编号 父件ID
----------- ---------- ---------- -----------
27 ANC00100 NULL NULL
28 ANA00070 ANC00250 30
29 ANA00080 ANC00250 30
30 ANC00250 ANC00010 31
31 ANC00010 ANC00100 27
32 ANA00070 ANC00250 30
33 ANA00080 ANC00250 30
34 ANC00250 ANC00100 27(所影响的行数为 8 行)
*/
insert @t select 27 , 'ANC00100' , 'NULL' , NULL
insert @t select 28 , 'ANA00070' , 'ANC00250' , NULL
insert @t select 29 , 'ANA00080' , 'ANC00250' , NULL
insert @t select 30 , 'ANC00250' , 'ANC00010' , NULL
insert @t select 31 , 'ANC00010' , 'ANC00100' , NULL
insert @t select 32 , 'ANA00070' , 'ANC00250' , NULL
insert @t select 33 , 'ANA00080' , 'ANC00250' , NULL
insert @t select 34 , 'ANC00250' , 'ANC00100' , NULL
update a set 父件ID = xh from @t a,(
select b.id,b.子件编号,父件编号,a.id xh
from (
select id,子件编号,(select count(*) from @t where a.子件编号=子件编号 and a.id>id) xh,
(select count(*) from @t where a.子件编号=子件编号) fzs from @t a
) a,@t b
where a.子件编号 = b.父件编号
and
(select count(*) from @t where 子件编号 = b.子件编号 and id < b.id)%fzs=a.xh--select b.id,b.子件编号,父件编号,(select count(*) from @t where 子件编号 = b.子件编号 and id < b.id)%fzs+a.id xh
--from (select 子件编号,min(id) id,count(*) fzs from @t group by 子件编号) a,@t b
--where a.子件编号 = b.父件编号
) b where a.id = b.id
select * from @t
/*
id 子件编号 父件编号 父件ID
----------- ---------- ---------- -----------
27 ANC00100 NULL NULL
28 ANA00070 ANC00250 30
29 ANA00080 ANC00250 30
30 ANC00250 ANC00010 31
31 ANC00010 ANC00100 27
32 ANA00070 ANC00250 31 这里的父件ID似乎应该是34
33 ANA00080 ANC00250 31 这里的父件ID似乎应该是34
34 ANC00250 ANC00100 27
*/ 32、33那两行为什么是34。而不是30??
declare @t table (id int,子件编号 varchar(10),父件编号 varchar(10),父件ID int)
insert @t select 27 , 'ANC00100' , 'NULL' , NULL
insert @t select 28 , 'ANA00070' , 'ANC00250' , NULL
insert @t select 29 , 'ANA00080' , 'ANC00250' , NULL
insert @t select 30 , 'ANC00250' , 'ANC00010' , NULL
insert @t select 31 , 'ANC00010' , 'ANC00100' , NULL
insert @t select 32 , 'ANA00070' , 'ANC00250' , NULL
insert @t select 33 , 'ANA00080' , 'ANC00250' , NULL
insert @t select 34 , 'ANC00250' , 'ANC00100' , NULL
update a set 父件ID = xh from @t a,(
select b.id,b.子件编号,父件编号,a.id xh
from (
select id,子件编号,(select count(*) from @t where a.子件编号=子件编号 and a.id>id) xh,
(select count(*) from @t where a.子件编号=子件编号) fzs from @t a
) a,@t b
where a.子件编号 = b.父件编号
and
(select count(*) from @t where 子件编号 = b.子件编号 and id < b.id)%fzs=a.xh--select b.id,b.子件编号,父件编号,(select count(*) from @t where 子件编号 = b.子件编号 and id < b.id)%fzs+a.id xh
--from (select 子件编号,min(id) id,count(*) fzs from @t group by 子件编号) a,@t b
--where a.子件编号 = b.父件编号
) b where a.id = b.id
select * from @t
/*
id 子件编号 父件编号 父件ID
----------- ---------- ---------- -----------
27 ANC00100 NULL NULL
28 ANA00070 ANC00250 30
29 ANA00080 ANC00250 30
30 ANC00250 ANC00010 31
31 ANC00010 ANC00100 27
32 ANA00070 ANC00250 34
33 ANA00080 ANC00250 34
34 ANC00250 ANC00100 27
*/
同时也感谢happyflystone,dawugui,pt1314917等大哥的一路支持和关注,虽然我也想解释一下为什么是34而不是30,不过我想了好久也解释不清,我只是知道我想要这样的结果,结贴了,再次谢谢你们!