从重复数据的几个字段中取出时间最近的一条数据
数据如下:
id, type, cid, uid, time
119, 2, 50, 2, 2015-05-18 08:11:38.077
120, 2, 51, 2, 2015-05-18 08:11:38.093
123, 2, 50, 64, 2015-05-19 14:44:08.123
124, 2, 51, 64, 2015-05-19 14:44:08.143
125, 2, 52, 64, 2015-05-19 14:44:08.147
126, 3, 54, 64, 2015-05-20 10:15:03.187
127, 2, 55, 64, 2015-05-20 10:15:03.283
139, 2, 50, 4, 2015-05-20 16:24:39.197
140, 2, 54, 4, 2015-05-20 16:28:40.067
328, 2, 58, 4, 2015-07-20 16:13:13.047
329, 5, 59, 4, 2015-07-20 16:13:13.060
330, 2, 57, 4, 2015-07-20 16:13:24.800
691, 2, 61, 4, 2015-08-21 16:40:40.797
694, 2, 65, 2, 2015-10-21 13:49:12.223
695, 2, 59, 2, 2015-10-21 14:22:10.630
697, 2, 247,2, 2015-10-23 00:00:00.000
698, 2, 62, 2, 2015-10-23 16:13:44.177
699, 2, 57, 2, 2015-10-23 17:41:55.520
根据以上数据写的语句如下:
select a.* from table a where a.type=2 and not exists(
select 1 from table
where uid=a.uid and time<a.time
)
结果如下:
id type cid uid time
-----------------------------------------------------------
123 2 50 64 2015-05-19 14:44:08.123
139 2 50 4 2015-05-20 16:24:39.197
为什么uid为2的没有出来 ,正确的SQL应是?