如何有条件的获取下一条记录中指定字段的值?
表
ID F1 F2 F3
001 DD 2007-04-18 13:05:31
001 AA 2007-04-21 10:25:54
001 CC 2007-04-22 09:05:04
002 DE 2007-04-18 13:08:56
002 AB 2007-04-21 11:55:54
003 BC 2007-04-22 10:05:22
003 XX 2007-05-03 14:22:16
...
想要取得的结果表
ID F1 F2 F3 F4 F5
001 DD 2007-04-18 13:05:31 2007-04-21 10:25:54
001 AA 2007-04-21 10:25:54 2007-04-22 09:05:04
001 CC 2007-04-22 09:05:04
002 DE 2007-04-18 13:08:56 2007-04-21 11:55:54
002 AB 2007-04-21 11:55:54
003 BC 2007-04-22 10:05:22 2007-05-03 14:22:16
003 XX 2007-05-03 14:22:16
...
说明:F4取同ID的下一笔记录中的F2的值,F5取取同ID的下一笔记录中的F3的值,同ID最后一笔记录F4,F5的值为空!
想在SQL2005里面实现,存储过程也行!
[解决办法]
drop table #t
go
create table #t(ID varchar(10),F1 varchar(10),F2 varchar(10),F3 varchar(10))
insert into #t
select '001 ', 'DD ', '2007-04-18 ', '13:05:31 '
union all select '001 ', 'AA ', '2007-04-21 ', '10:25:54 '
union all select '001 ', 'CC ', '2007-04-22 ', '09:05:04 '
union all select '002 ', 'DE ', '2007-04-18 ', '13:08:56 '
union all select '002 ', 'AB ', '2007-04-21 ', '11:55:54 '
union all select '003 ', 'BC ', '2007-04-22 ', '10:05:22 '
union all select '003 ', 'XX ', '2007-05-03 ', '14:22:16 '
alter table #t add id1 int identity(1,1)
go
select *,
(select top 1 F2 from #t b where b.ID=a.ID and b.id1> a.id1) as F4,
(select top 1 F3 from #t c where c.ID=a.ID and c.id1> a.id1) as F5
from #t a
/*
ID F1 F2 F3 id1 F4 F5
---------- ---------- ---------- ---------- ----------- ---------- ----------
001 DD 2007-04-18 13:05:31 1 2007-04-21 10:25:54
001 AA 2007-04-21 10:25:54 2 2007-04-22 09:05:04
001 CC 2007-04-22 09:05:04 3 NULL NULL
002 DE 2007-04-18 13:08:56 4 2007-04-21 11:55:54
002 AB 2007-04-21 11:55:54 5 NULL NULL
003 BC 2007-04-22 10:05:22 6 2007-05-03 14:22:16
003 XX 2007-05-03 14:22:16 7 NULL NULL
（所影响的行数为 7 行）
*/
alter table #t drop column id1
go
[解决办法]
try:
select a.*,b.f2 as f4,b.f3 as f5
from 表 a
left join 表 b
on a.id=b.id
and b.f2=(select min(F2) from 表 where id=a.id and f2> a.f2)
[解决办法]
借用gahade(与君共勉)测试数据:
create table #t(ID varchar(10),F1 varchar(10),F2 varchar(10),F3 varchar(10))
go
insert into #t
select '001 ', 'DD ', '2007-04-18 ', '13:05:31 '
union all select '001 ', 'AA ', '2007-04-21 ', '10:25:54 '
union all select '001 ', 'CC ', '2007-04-22 ', '09:05:04 '
union all select '002 ', 'DE ', '2007-04-18 ', '13:08:56 '
union all select '002 ', 'AB ', '2007-04-21 ', '11:55:54 '
union all select '003 ', 'BC ', '2007-04-22 ', '10:05:22 '
union all select '003 ', 'XX ', '2007-05-03 ', '14:22:16 '
alter table #t add id1 int identity(1,1)
select a.id,a.f1,a.f2,a.f3,b.f2 as f4,b.f3 as f5 from #t a left join #t b on a.id=b.id and a.id1=b.id1-1
alter table #t drop column id1
--drop table #t
