数据库sTable结构如下:sId sTime sValue1 2009-1-1 10:01:25 10
1 2009-1-1 12:22:22 9
1 2009-1-1 18:15:26 10
2 2009-1-1 10:01:25 20
2 2009-1-1 12:14:10 18
3 2009-1-1 10:30:30 171 2009-1-2 10:20:14 15
2 2009-1-2 10:25:20 13
2 2009-1-2 14:14:10 12
3 2009-1-2 11:25:25 25
3 2009-1-2 15:22:22 22结果如下:1 2009-1-1 18:15:26 10
2 2009-1-1 12:14:10 18
3 2009-1-1 10:30:30 171 2009-1-2 10:20:14 15
2 2009-1-2 14:14:10 12
3 2009-1-2 15:22:22 22也就是说,如果日期一样,sId一样,取最晚的时间的记录。在线等,谢谢了。
1 2009-1-1 12:22:22 9
1 2009-1-1 18:15:26 10
2 2009-1-1 10:01:25 20
2 2009-1-1 12:14:10 18
3 2009-1-1 10:30:30 171 2009-1-2 10:20:14 15
2 2009-1-2 10:25:20 13
2 2009-1-2 14:14:10 12
3 2009-1-2 11:25:25 25
3 2009-1-2 15:22:22 22结果如下:1 2009-1-1 18:15:26 10
2 2009-1-1 12:14:10 18
3 2009-1-1 10:30:30 171 2009-1-2 10:20:14 15
2 2009-1-2 14:14:10 12
3 2009-1-2 15:22:22 22也就是说,如果日期一样,sId一样,取最晚的时间的记录。在线等,谢谢了。
from tb
group by sId,sTime
from tb
group by sId,sTime
insert into stable values(1 ,'2009-1-1 10:01:25', 10 )
insert into stable values(1 ,'2009-1-1 12:22:22', 9 )
insert into stable values(1 ,'2009-1-1 18:15:26', 10 )
insert into stable values(2 ,'2009-1-1 10:01:25', 20 )
insert into stable values(2 ,'2009-1-1 12:14:10', 18 )
insert into stable values(3 ,'2009-1-1 10:30:30', 17 )
insert into stable values(1 ,'2009-1-2 10:20:14', 15 )
insert into stable values(2 ,'2009-1-2 10:25:20', 13 )
insert into stable values(2 ,'2009-1-2 14:14:10', 12 )
insert into stable values(3 ,'2009-1-2 11:25:25', 25 )
insert into stable values(3 ,'2009-1-2 15:22:22', 22 )
goselect t.* from stable t where convert(varchar(8),stime,114) = (select max(convert(varchar(8),stime,114)) from stable where sid = t.sid and convert(varchar(10),stime,120) = convert(varchar(10),t.stime,120))
/*
sId sTime sValue
----------- ------------------------------------------------------ -----------
1 2009-01-01 18:15:26.000 10
2 2009-01-01 12:14:10.000 18
3 2009-01-01 10:30:30.000 17
1 2009-01-02 10:20:14.000 15
2 2009-01-02 14:14:10.000 12
3 2009-01-02 15:22:22.000 22(所影响的行数为 6 行)
*/select t.* from stable t where not exists (select 1 from stable where sid = t.sid and convert(varchar(10),stime,120) = convert(varchar(10),t.stime,120) and convert(varchar(8),stime,114) > convert(varchar(8),t.stime,114))
/*
sId sTime sValue
----------- ------------------------------------------------------ -----------
1 2009-01-01 18:15:26.000 10
2 2009-01-01 12:14:10.000 18
3 2009-01-01 10:30:30.000 17
1 2009-01-02 10:20:14.000 15
2 2009-01-02 14:14:10.000 12
3 2009-01-02 15:22:22.000 22(所影响的行数为 6 行)
*/drop table stable