记录合并问题发觉自己好差劲啊.这都解决不了
id     sid     ptime
1       2         2007-03-01
2       3         2007-01-01
3       2         2007-02-01
4       2         2007-01-01
5       3         2007-03-01
要求生成的数据为
sid   lweek               pweek
2       2007-03-01     2007-02-01
3       2007-03-01     2007-01-01
sid是一个分类.我要找出2,3这两个分类的最近两条记录,并将时间合并

------解决方案--------------------


Create Table TEST
(id Int,
sid Int,
ptime Varchar(10))
Insert TEST Select 1, 2, '2007-03-01 '
Union All Select 2, 3, '2007-01-01 '
Union All Select 3, 2, '2007-02-01 '
Union All Select 4, 2, '2007-01-01 '
Union All Select 5, 3, '2007-03-01 '
GO
Select
sid,
Max(ptime) As lweek,
(Select TOP 1 ptime From TEST B Where ptime < (Select Max(ptime) From TEST Where sid = B.sid) And sid = A.sid Order By ptime Desc) As pweek
From TEST A
Group By sid
GO
Drop Table TEST
/*
sid lweek pweek
2 2007-03-01 2007-02-01
3 2007-03-01 2007-01-01
*/