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

The first SQL: wrong id value of x y
Select id, x, y, device_id, from_unixtime (Max (locate_time)/1000, '% % y - m - H: % d % % I: % S') times the from
Device_position where build_id='862300010010300012'
And floor='f1' group by device_id;




Article 2 the term "SQL: query the records are correct, but to check again, what is the optimal place?
The select b.i d, b.x, b.y, a. d. evice_id, a. ocate_time from (
The 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. ocate_time=b.l ocate_time

CodePudding user response:

Analytical functions can be used to solve

 
Select id, x, y, device_id, Max (locate_time) over (partition by device_id) locate_time 
The from device_position 
Where build_id='862300010010300012' 
='f1' and floor; 

CodePudding user response:

In view of the device_id is not the only, need to add a distinct SQL heavy

 
Select distinct id, x, y, device_id, Max (locate_time) over (partition by device_id) locate_time 
The from device_position 
Where build_id='862300010010300012' 
='f1' and floor; 

CodePudding user response:

Another writing: the row_number, higher performance

 
Select id, x, y, device_id, locate_time 
The from (select id, x, y, device_id, locate_time, row_number () over (partition by device_id order by locate_time desc) rn 
The from device_position 
Where build_id='862300010010300012' 
And floor='f1' 
) 
Where an rn=1 

CodePudding user response:

Use the returning not line

CodePudding user response:

Use the last method I said above, problem has been solved!
Building knot stick!