如何生成这样的结果：
数据表：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
[解决办法]
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)
from
tab1 t
where
t.Ass_no is not null
[解决办法]
declare @tab1 table(ID int,Parent_ID int,Ass_no varchar(10),F_no varchar(10))
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
*/
[解决办法]
create table T(ID int, Parent_ID int, Ass_no varchar(10), F_no varchar(10))
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)
[解决办法]
create table A(ID int identity(1,1), Parent_ID int, Ass_no varchar(20), F_no varchar(20))
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, ' ') <> ' '
