Creating new column with rows shifted up in MySql

问题:

I have following table T1 :

ID | time
10 | 1000
10 | 1002
10 | 1003
11 | 1002
11 | 1004

Now I want to create a table T2 from T1 in which for each ID, successive time-intervals are shown i.e.

ID | time1 | time 2
10 | 1000 | 1002
10 | 1002 | 1003
10 | 1003 | NULL
11 | 1002 | 1004
11 | 1004 | NULL

so time2 entry is basically the entry of time1 in next row.
How can I do it in MySql ?

回答1:

Have a try with this one:

SELECT
ID,
`time` AS time1,
(SELECT MIN(st.`time`) FROM T1 st WHERE st.ID = T1.ID AND st.`time` > T1.`time`) AS time2
FROM T1