根据字段唯一查询

[yunmoon]

表 RunningStatus

PuID GpsTime Longitude Speed Rese

1 1 3 1 0
1 2 4 2 0
2 3 5 3 0
2 4 6 4 0
2 4 7 5 0
3 5 7 6 0


要求：查询 PuID唯一 GpsTime最大的所有信息
如下：
1 2 4 2 0
2 4 6 4 0
3 5 7 6 0

语句：select a.* from (SELECT PuID,max(GpsTime) as GpsTime FROM RunningStatus group by PuID) b LEFT OUTER JOIN RunningStatus a on a.PuID = b.PuID and a.GpsTime = b.GpsTime

得到的是：
1 2 4 2 0
2 4 6 4 0
2 4 7 5 0
3 5 7 6 0


语句：再嵌套一层group by ：
select a.* from (SELECT PuID,max(GpsTime) as GpsTime FROM RunningStatus group by PuID) b LEFT OUTER JOIN RunningStatus a on a.PuID = b.PuID and a.GpsTime = b.GpsTime and a.Longitude in (select max(Longitude) as Longitude from (select a.* from (SELECT PuID,max(GpsTime) as GpsTime FROM RunningStatus group by PuID) b LEFT OUTER JOIN RunningStatus a on a.PuID = b.PuID and a.GpsTime = b.GpsTime) c group by c.PuID) order by PuID

得到了想要的数据。

但是如果Longitude字段又不唯一的话就又要再嵌套一层group by 。。。。。

所以能不能有种简单的方法？？

[水族杰纶]

加個自增列~~

[csdyyr]

需求不明确。

[jinjazz]

select * from (select *,row_number()over(partition by PuID order by GpsTime) as rn from tb) a where rn=1

[sdhdy]

--怎么写的这么复杂？这个不就是楼主想要的第一个结果？
declare @tb table(PuID int, GpsTime int, Longitude int, Speed int, Rese int)
insert @tb select 1,      1,      3,        1,    0    
insert @tb select 1,      2,      4,        2,    0  
insert @tb select 2,      3,      5,        3,    0  
insert @tb select 2,      4,      6,        4 ,   0  
insert @tb select 2,      4,      7,        5,    0    
insert @tb select 3,      5,      7,        6 ,   0 
select * from @tb a where not exists(select 1 from @tb where PuID=a.PuID 
and (GpsTime>a.GpsTime or (GpsTime=a.GpsTime and Longitude<a.Longitude)))

/*
PuID        GpsTime     Longitude   Speed       Rese        
----------- ----------- ----------- ----------- ----------- 
1           2           4           2           0
2           4           6           4           0
3           5           7           6           0

（所影响的行数为 3 行）
*/

[幸运的意外]

select * from @tb a where not exists(select 1 from @tb where PuID=a.PuID
and (GpsTime>a.GpsTime or (GpsTime=a.GpsTime and Longitude<a.Longitude)))

[百年树人]

---测试数据---
if object_id('[RunningStatus]') is not null drop table [RunningStatus]
go
create table [RunningStatus]([PuID] int,[GpsTime] int,[Longitude] int,[Speed] int,[Rese] int)
insert [RunningStatus]
select 1,1,3,1,0 union all
select 1,2,4,2,0 union all
select 2,3,5,3,0 union all
select 2,4,6,4,0 union all
select 2,4,7,5,0 union all
select 3,5,7,6,0
 
---查询---
select 
 *
from 
  [RunningStatus] t
where 
  not exists(select 1 
             from RunningStatus 
             where puid=t.puid 
             and (GpsTime>t.GpsTime 
                 or GpsTime=t.GpsTime 
                 and (Longitude<t.Longitude 
                   or Longitude=t.Longitude 
                   and Speed>t.Speed)))


---结果---
PuID        GpsTime     Longitude   Speed       Rese        
----------- ----------- ----------- ----------- ----------- 
1           2           4           2           0
2           4           6           4           0
3           5           7           6           0

（所影响的行数为 3 行）

[sdhdy]

--如果想要Longitude字段唯一，那就再来个分组。
declare @tb table(PuID int, GpsTime int, Longitude int, Speed int, Rese int)
insert @tb select 1,      1,      3,        1,    0    
insert @tb select 1,      2,      4,        2,    0  
insert @tb select 2,      3,      5,        3,    0  
insert @tb select 2,      4,      6,        4 ,   0  
insert @tb select 2,      4,      7,        5,    0    
insert @tb select 3,      5,      7,        6 ,   0 
--再嵌套一下，或借用个临时表，速度更快。
select * into #temp from @tb a where not exists(select 1 from @tb where PuID=a.PuID 
and (GpsTime>a.GpsTime or (GpsTime=a.GpsTime and Longitude<a.Longitude)))

select PuID,GpsTime,Longitude=max(Longitude),Speed,Rese from #temp
group by PuID,GpsTime,Speed,Rese

drop table #temp
/*
PuID        GpsTime     Longitude   Speed       Rese        
----------- ----------- ----------- ----------- ----------- 
1           2           4           2           0
2           4           6           4           0
3           5           7           6           0

（所影响的行数为 3 行）
*/

[yunmoon]

谢谢 sdhdy

[yunmoon]

但是如果数据很多的时候
select * from @tb a where not exists(select 1 from @tb where PuID=a.PuID
and (GpsTime>a.GpsTime or (GpsTime=a.GpsTime and Longitude<a.Longitude)))
用这个好像速度不行？？