一个搜索记录集问题。
如何从A记录集中搜索出结果记录集B:
A:
ID, DATE
1 20030909
1 20030606
1 20030308
1 20021010
2 20030906
2 20030706
2 20030501
....
B:
ID DATE1 DATE2
1 20030909 20030606
1 20030606 20030308
1 20030308 20021010
2 20030906 20030706
2 20030706 20030501
2 20030501 NULL
.......
A记录集中每个同ID纪录已经知道最多有4条，A中的纪录已经排好序。
B记录集中每个同ID纪录要求最多有3条。其中DATE2的每一条值等于DATE1的下一条值。谢谢大家。
问题点数：0、回复次数：6Top
1 楼victorycyz（--）回复于 2003-12-01 16:02:04 得分 0
没看懂。Top
2 楼Rotaxe（程序员）回复于 2003-12-01 16:03:36 得分 0
按什么排序？Top
3 楼goodluck001（日死小日本）回复于 2003-12-01 16:29:20 得分 0
alter table TableName add NID int identity(1,1)
go
select a.*,(select [Date] from TableName where nid=a.nid+1)
from TableName a
alter table TableName drop column NID
Top
4 楼wzh1215（懒猫）回复于 2003-12-01 16:32:07 得分 0
create table #aa(id int,DATE datetime)
insert #aa values(1,'20030909')
insert #aa values(1,'20030606')
insert #aa values(1,'20030308')
insert #aa values(1,'20021010')
insert #aa values(2,'20030906')
insert #aa values(2,'20030706')
insert #aa values(2,'20030501')
select * from #aa order by id,date desc
select id,DATE1,DATE2 from
(select b.id,b.date as DATE1,(select count(*) from #aa c where b.id=c.id and c.date>=b.date)as no,(select max(a.date) from #aa a where a.id=b.id and a.date<b.date) as DATE2 from #aa b --order by b.id,b.date desc
) tmp
where no<>'4' order by id,DATE1Top
5 楼wzh1215（懒猫）回复于 2003-12-01 16:33:51 得分 0
select id,DATE1,DATE2 from
(select b.id,b.date as DATE1,(select count(*) from #aa c where b.id=c.id and c.date>=b.date)as no,(select max(a.date) from #aa a where a.id=b.id and a.date<b.date) as DATE2 from #aa b --order by b.id,b.date desc
) tmp
where no<>'4' order by id,DATE1 descTop
6 楼shuishi（睡狮）回复于 2003-12-01 16:35:05 得分 0
A记录集中是按照ID和DATE排的序。有没有简单的办法？Top
