表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

解决方案 »

  1.   

    可以使用分析函数解决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' ;
      

  2.   

    鉴于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' ;
      

  3.   

    换种写法: 采用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
      

  4.   

    使用returning不行吗