表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
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
from device_position
where build_id = '862300010010300012'
and floor = 'f1' ;
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' ;
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