数据表:tab1
ID, Parent_ID, Ass_no, F_no
1 123 FA0086 null
2 123 null D00123
3 123 001988 null
4 123 FA0090 null
5 123 null D01897
6 120 FA0086 null
7 120 null D00125
8 120 20201 null
9 120 FA0090 null
10 120 null D01899
说明:ID是流水号,自动生成。
Parent_ID是主板的代号
Ass_no是组件的组合号,一个组合号对应一个F_no或者不对应有F_no,如果该组件有编号,在数据库表中其编号一定紧跟在后面一行。
F_no是组件的编号,不重复。
现在要生成这样的结果:(请问如何写这样的SQL语句,分不够再加,谢啦)
Parent_ID, Ass_no, F_no
123 FA0086 D00123
123 001988 null
123 FA0090 D01897
120 FA0086 D00125
120 20201 null
120 FA0090 D01899
ID, Parent_ID, Ass_no, F_no
1 123 FA0086 null
2 123 null D00123
3 123 001988 null
4 123 FA0090 null
5 123 null D01897
6 120 FA0086 null
7 120 null D00125
8 120 20201 null
9 120 FA0090 null
10 120 null D01899
说明:ID是流水号,自动生成。
Parent_ID是主板的代号
Ass_no是组件的组合号,一个组合号对应一个F_no或者不对应有F_no,如果该组件有编号,在数据库表中其编号一定紧跟在后面一行。
F_no是组件的编号,不重复。
现在要生成这样的结果:(请问如何写这样的SQL语句,分不够再加,谢啦)
Parent_ID, Ass_no, F_no
123 FA0086 D00123
123 001988 null
123 FA0090 D01897
120 FA0086 D00125
120 20201 null
120 FA0090 D01899
t.Parent_ID,t.Ass_no,(select top 1 F_no from tab1 where ID>t.ID and Parent_ID=t.Parent_ID order by ID)
from
tab1 t
where
t.Ass_no is not null
insert into @tab1 select 1 ,123,'FA0086', null
insert into @tab1 select 2 ,123, null , 'D00123'
insert into @tab1 select 3 ,123,'001988', null
insert into @tab1 select 4 ,123,'FA0090', null
insert into @tab1 select 5 ,123,null , 'D01897'
insert into @tab1 select 6 ,120,'FA0086', null
insert into @tab1 select 7 ,120,null , 'D00125'
insert into @tab1 select 8 ,120,'20201' , null
insert into @tab1 select 9 ,120,'FA0090', null
insert into @tab1 select 10,120,null , 'D01899'select
t.Parent_ID,t.Ass_no,
(select top 1 F_no from @tab1 where ID>t.ID and Parent_ID=t.Parent_ID order by ID) as F_no
from
@tab1 t
where
t.Ass_no is not null/*
Parent_ID Ass_no F_no
----------- ---------- ----------
123 FA0086 D00123
123 001988 NULL
123 FA0090 D01897
120 FA0086 D00125
120 20201 NULL
120 FA0090 D01899
*/
insert T select 1, 123, 'FA0086', null
union all select 2, 123, null, 'D00123'
union all select 3, 123, '001988', null
union all select 4, 123, 'FA0090', null
union all select 5, 123, null, 'D01897'
union all select 6, 120, 'FA0086', null
union all select 7, 120, null, 'D00125'
union all select 8, 120, '20201', null
union all select 9, 120, 'FA0090', null
union all select 10, 120, null, 'D01899'select Parent_ID, Ass_no,
F_no=(select F_no from T where ID=tmp.ID+1)
from T as tmp
where Ass_no is not null--result
Parent_ID Ass_no F_no
----------- ---------- ----------
123 FA0086 D00123
123 001988 NULL
123 FA0090 D01897
120 FA0086 D00125
120 20201 NULL
120 FA0090 D01899(6 row(s) affected)
insert A(Parent_ID, Ass_no, F_no)
select 123,'FA0086','' union all
select 123,'','D00123' union all
select 123,'001988','' union all
select 123,'FA0090','' union all
select 123,'','D01897' union all
select 120,'FA0086','' union all
select 120,'','D00125' union all
select 120,'20201','' union all
select 120,'FA0090','' union all
select 120,'','D01899'
select Parent_ID,
Ass_No,
F_No =(select F_No from A B where B.ID =C.ID +1)
from A C
where isnull(C.Ass_No,'')<>''