The Table A

J0A0HG7L corresponding to four time, how to draw a third time or the second time,

Column 1 column 2 column 3
44 2016/10/8 J0A0HG7L morning 02:35:54
46 2016/10/9 J0A0HG7L morning 03:48:26
49 2016/10/10 J0A0HG7L morning 03:18:53
51 2016/10/11 J0A0HG7L morning 03:38:15

If you want to draw the last pen next to bottom time and time difference of more than 30 minutes, and SQL

PS: 1 min (column 3) can be used, the last one can use the Max (column 3)

Temporarily suspended
o

CodePudding user response:

- use row_number, serial number,
- rn=1, is the final data, rn)=2, is the penultimate data
- grammar reference is as follows:
Select t. *, row_number (partition by column 1 order by column 3 desc) rn from t

CodePudding user response:

With tt as (
The select column 1, t. t. column 3, t. column 3, row_number () over (partition by t. column 1 order by t. column 3 desc) rn from tab_name t)
Select the from tt t1, t2 tt
Where a t1. Column 1=t2. Column 1 and t1. Rn=1 and t1. Rn=t2. Rn - 1
And (t1) column 3 - t2) column 3) & gt; 30/60/24

CodePudding user response:

According to the business rules with row_number sorting, according to the need to take the corresponding serial number of the record

CodePudding user response:

 
Select nth_value (column 3, 1) over (partition 1 order by column by column 2 desc) n1_val, 
 - the last timeNth_value (column 3, 2) over (partition 1 order by column by column 2 desc) n2_val, 
 - the 2nd timeA. * 
The from the TAB a 

CodePudding user response:

Mark temporarily didn't see on the first floor writing SQL..