求次大时间的记录
表格如下：
id num time
01 2 9:10
01 1 9:11
01 4 9:27
01 1.2 10:10
03 10 10:15
03 17 10:25
03 13 10:35
03 12 10:45
03 1 10:55
求：
id num
01 4
03 12
[解决办法]
Create Table TEST
(id Char(2),
num Int,
[time] Varchar(10))
Insert TEST Select '01 ', 2, '9:10 '
Union All Select '01 ', 1, '9:11 '
Union All Select '01 ', 4, '9:27 '
Union All Select '01 ', 12, '10:10 '
Union All Select '03 ', 10, '10:15 '
Union All Select '03 ', 17, '10:25 '
Union All Select '03 ', 13, '10:35 '
Union All Select '03 ', 12, '10:45 '
Union All Select '03 ', 1, '10:55 '
GO
Select A.id, A.num From TEST A Where (Select Count(id) From TEST Where id = A.id And Cast( '1900-01-01 ' + [time] As DateTime) > Cast( '1900-01-01 ' + A.[time] As DateTime)) = 1
GO
Drop Table TEST
--Result
/*
idnum
014
0312
*/
