记录合并问题发觉自己好差劲啊.这都解决不了
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
(idInt,
sidInt,
ptimeVarchar(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
/*
sidlweekpweek
22007-03-012007-02-01
32007-03-012007-01-01
*/
