mysql - MySQL 中基于位置的操作

Question

我想运行一个查询，使用数组索引可以很容易地在命令式语言中使用数组。我有下表:

value  id
10     x1
20     x2
15     x3
25     x4
30     x5
31     x6

我想计算一对相邻值的差异，例如:

value
20 - 10
25 - 15
31 - 30

我只知道 x6>x5>...>x1。我不知道如何使用 MySQL 执行此操作。

Answer 1

假设您有一种对数据进行排序的方法，您可以生成行号并将奇数行连接到偶数行

drop table if exists t;
create table t(value int, id varchar(2));

insert into t values
(10   ,  'x1'),
(20   ,  'x2'),
(15   ,  'x3'),
(25   ,  'x4'),
(30   ,  'x5'),
(31   ,  'x6');

select s.*,a.*
from
(select id, value , 
         @rn:=@rn + 1 rn
from t
cross join (select @rn:=0) r
order by id
) a
join
(
select id, value , 
         @rn1:=@rn1 + 1 rn1
from t
cross join (select @rn1:=0) r
order by id
) s
on s.rn1 = rn + 1
where a.rn % 2 > 0
;
+------+-------+------+------+-------+------+
| id   | value | rn1  | id   | value | rn   |
+------+-------+------+------+-------+------+
| x2   |    20 |    2 | x1   |    10 |    1 |
| x4   |    25 |    4 | x3   |    15 |    3 |
| x6   |    31 |    6 | x5   |    30 |    5 |
+------+-------+------+------+-------+------+
3 rows in set (0.00 sec)