table
--------------------------
id,num1,num2, time
1 2 2 2012-11-28 12:32:07
2 3 3 2012-11-28 12:32:09
3 2 3 2012-11-28 12:32:08
4 4 8 2012-11-28 12:32:10
3 5 4 2012-11-28 12:32:17
2 2 2 2012-11-28 12:32:12
。取10秒内 按id分组的数据 重复的id取最新的那个
比如编号为2的id有两个分组后 取 2 2 2 2012-11-28 12:32:12最新的那个查询结果如下
--------------------------
id,num1,num2, time
1 2 2 2012-11-28 12:32:07
4 4 8 2012-11-28 12:32:10
3 5 4 2012-11-28 12:32:17
2 2 2 2012-11-28 12:32:12
--------------------------
id,num1,num2, time
1 2 2 2012-11-28 12:32:07
2 3 3 2012-11-28 12:32:09
3 2 3 2012-11-28 12:32:08
4 4 8 2012-11-28 12:32:10
3 5 4 2012-11-28 12:32:17
2 2 2 2012-11-28 12:32:12
。取10秒内 按id分组的数据 重复的id取最新的那个
比如编号为2的id有两个分组后 取 2 2 2 2012-11-28 12:32:12最新的那个查询结果如下
--------------------------
id,num1,num2, time
1 2 2 2012-11-28 12:32:07
4 4 8 2012-11-28 12:32:10
3 5 4 2012-11-28 12:32:17
2 2 2 2012-11-28 12:32:12
SELECT a.id,a.num1,a.num2, a.time
FROM tab a,
(SELECT MAX(time) btime,ID
FROM tab
GROUP BY ID ) bWHERE a.ID=b.ID
AND a.time=b.btime
试试
select t.*, row_number() over (partition by id order by time desc) rn from t
) where rn = 1;