SQL使用MAX()函数时怎么获取该条记录的其他信息

[oschina_40599201]

表TABLE_A
id x y device_id locate_time
1 189405 138762 0000200101b4 1484720129552
2 297699 88270 0000200101b5 1484721110698
3 297699 138762 0000200101b6 1484723110905
4 297600 133362 0000200101b5 1484723110956
5 297666 123333 0000200101b5 1484723113956

第一条sql: id x y的值有误
select id, x, y, device_id, from_unixtime(max(locate_time)/1000, '%Y-%m-%d %H:%i:%S') times from
device_position where build_id = '862300010010300012'
and floor = 'f1' group by device_id;




第二条sql: 查询出记录是正确的,但是重新查了一遍,还有什么优化的地方？
select b.id, b.x,b.y,a.device_id, a.locate_time from (
select device_id, max(locate_time) locate_time from
device_position where build_id = '862300010010300012'
and floor = 'f1' group by device_id
) a LEFT JOIN (
select id,x,y,build_id,floor,locate_time from device_position
) b on a.locate_time = b.locate_time

[碧水幽幽泉]

可以使用分析函数解决

select id, x,y,device_id, max(locate_time)over(partition by device_id) locate_time
    from device_position
  where  build_id = '862300010010300012'
      and floor = 'f1' ;

[碧水幽幽泉]

鉴于device_id不是唯一的，SQL需要加个distinct去重

select distinct id, x,y,device_id, max(locate_time)over(partition by device_id) locate_time
    from device_position
  where  build_id = '862300010010300012'
      and floor = 'f1' ;

[碧水幽幽泉]

换种写法: 采用row_number，性能更高

 select id,x,y,device_id,locate_time
     from(select id,x,y,device_id,locate_time,row_number()over(partition by device_id order by locate_time desc) rn
                 from device_position
               where  build_id = '862300010010300012'
                   and floor = 'f1' 
               )
    where rn = 1

[wandier]

使用returning不行吗

[碧水幽幽泉]

使用我上面说的最后一种方法，问题已解决！ 此楼结贴哈！