如何获取不重复的最新记录

[wen1512]

表结构及数据如下：

如何获取满足这两个条件的记录：
1.最新的（根据Inputtime_t判断最新的数据）；
2、CarID_i不重复的记录。

[q465897859]

select * from （select *,
row_number() over(partition by CarID_i order by Inputtime_t desc) rid
from 表） a where rid=1

[风一样的大叔]

[Quote=引用 1 楼 josy 的回复:]

SQL code
select *
from tb t
where not exists(select 1 from tb where CarID_i=t.CarID_i and Inputtime_t>t.Inputtime_t)
[/Quote]这个可以，俺试过

[苦苦的潜行者]

select * from t1 a
where not exists(select 1 from t1 where a.Inputtime_t<Inputtime_t and a.CarID_i=CarID_i)

[叶子]

select * from tablename t
where Inputtime_t=(select max(Inputtime_t) from tablename where CarID_i=t.CarID_i)

[百年树人]

select *
from tb t
where not exists(select 1 from tb where CarID_i=t.CarID_i and Inputtime_t>t.Inputtime_t)

[唐诗三百首]

楼主的图挂了,

[snbxp]

select * from tb
where not exists(select 1 from tb where cardid=t.card_id and inputtime_t>t.inputtime_t)

[EnForGrass]

[Quote=引用 1 楼 josy 的回复:]

SQL code
select *
from tb t
where not exists(select 1 from tb where CarID_i=t.CarID_i and Inputtime_t>t.Inputtime_t)
[/Quote]

支持树哥一个

[唐诗三百首]

try this,

with t as
(
select *,
row_number() over(partition by CarID_i order by Inputtime_t desc) rn
from tab
)
select *
from t where rn=1

[dawugui]

select t.* from tb t where Inputtime_t = (select max(Inputtime_t) from tb where CarID_i = t.CarID_i) order by t.CarID_i


select t.* from tb t where not exists (select 1 from tb where CarID_i = t.CarID_i and Inputtime_t > t.Inputtime_t) order by t.CarID_i

[一十七]

[Quote=引用 5 楼 ap0405140 的回复:]
try this,

SQL code

with t as
(
select *,
row_number() over(partition by CarID_i order by Inputtime_t desc) rn
from tab
)
select *
from t where rn=1
[/Quote]


效率比较高

[chrisziling]

楼主，你这个问题和我问的问题太像了~而且 表的结构也差不多~不知道你的字段里有没有时间datatime
如果有的话~在查询里限定一个时间范围~查询的时间大大减少~~你试一下~

select * from tb a where GpsID_i = (select max(GpsID_i) from tb where (Inputtime_t between '2012-01-19 8:30' and '2012-01-19 22:30:00') AND CarID_i =a.CarID_i )

Inputtime_t 时间可设置成当前时间再小点 到当天12点之前 有个前提是GpsID_i是自动增长且无重复
你试一下

PS.我的帖子还没有结，目的是获取一条线路的最新轨迹，可以交流一下！
http://topic.csdn.net/u/20120116/15/b57c181c-6024-4def-b3bb-b7d742e7e735.html