更新时间:2023-01-29 15:56:40
这里有一个解决方案,在MySQL中返回你想要的内容
Here is a solution that returns what you want in MySQL
SET @a :=0;
SET @b :=2;
SELECT r.id, r.value, r.value/r2.value AS 'lag'
FROM
(SELECT if(@a, @a:=@a+1, @a:=1) as rownum, id, value FROM results) AS r
LEFT JOIN
(SELECT if(@b, @b:=@b+1, @b:=1) as rownum, id, value FROM results) AS r2
ON r.rownum = r2.rownum
MySQL 5.1 不喜欢针对子查询的自联接,因此您必须对行进行两次计数,因此不像它可能的那样整洁或可扩展,但它确实使指定滞后变得简单.
MySQL 5.1 doesn't like a self join against a subquery so you have to count rows twice, so not as tidy or scalable as it might be, but it does make specifying the lag simple.
对于使用 Oracle 的读者来说,这更容易
For readers that use Oracle instead this is much easier
SELECT id, value, value/lag(value, 2) over (order by id) as lag from results;