表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
uj5u.com熱心網友回復：
可以使用分析函式解決
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' ;
uj5u.com熱心網友回復：
鑒于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' ;
uj5u.com熱心網友回復：
換種寫法: 采用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
uj5u.com熱心網友回復：
使用returning不行嗎uj5u.com熱心網友回復：
使用我上面說的最后一種方法，問題已解決！此樓結貼哈！
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/81511.html
標籤：開發