日期:2014-05-18  浏览次数:20525 次

求次大时间的记录

表格如下:
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
/*
id num
01 4
03 12
*/