mysql - 如何将此postgresql lag语句移植到mysql？

Question

想象一个表刚刚填充了 ID 和创建的时间戳，我如何将其转换为 MySQL？:

SELECT created AS col_a , LAG (created) OVER ( ORDER by created ) AS col_b
FROM tester

Answer 1

您可以使用相关子查询:

SELECT t1.created AS col_a,
       (SELECT created
        FROM tester AS t2
        WHERE t2.created < t1.created
        ORDER BY created DESC LIMIT 1) AS col_b
FROM tester AS t1

或者，使用变量:

   SELECT t1.created AS col_a, t2.created AS col_b
   FROM (
     SELECT created, @rn1 := @rn1 + 1 AS rn
     FROM tester
     CROSS JOIN (SELECT @rn1 := 0) AS var
     ORDER BY created) AS t1
   LEFT JOIN (
     SELECT created, @rn2 := @rn2 + 1 AS rn
     FROM tester
     CROSS JOIN (SELECT @rn2 := 0) AS var
     ORDER BY created
   ) AS t2 ON t1.rn = t2.rn + 1