Mysql 选择子(行，行 - 1)

Question

如何从用户中选择user_id，SUB(row, row - 1)，其中user_id=@userid我的表用户，id 为 1、3、4、10、11、23...(不是++)

--id---------user_id------unixtime--
635918634   1529906793  1374253286
635918635   1529906793  1374253287
635957809   1529906793  1374253583
635957810   1529906793  1374253883
635957811   1529906793  1374254183

我希望

--id-----------user_id--------unixtime-------**SUB(row, row -1)**
635918634   1529906793  1374253286      0
635918635   1529906793  1374253287      1   
635957809   1529906793  1374253583      296     
635957810   1529906793  1374253883      300
635957811   1529906793  1374254183      300

使用 SUB(row, row -1):(unixtime 行 - unixtime 附近行之前)

Answer 1

使用用户变量添加序列(使用2个不同的起始序列号)，然后加入序列

SELECT Sub1.id, Sub1.user_id, Sub1.unixtime, Sub1.unixtime - IFNULL(Sub2.unixtime, 0)
FROM
(
    SELECT id, user_id, unixtime, @row = @row + 1 AS RowNum
    FROM SomeTable
    CROSS JOIN (SELECT @row = 1) t
    ORDER BY user_id, unixtime
) Sub1
LEFT OUTER JOIN
(
    SELECT id, user_id, unixtime, @row = @row + 1 AS RowNum
    FROM SomeTable
    CROSS JOIN (SELECT @row = 0) t
    ORDER BY user_id, unixtime
) Sub2
ON Sub1.RowNum = Sub2.RowNum

(未测试)