mysql 计算相邻2行某列的差值

1、原表数据
SELECT id, DATE, LIST, kwh
FROM power
WHERE LIST IN (‘AA20300’, ‘AA20315’, ‘AA20328’, ‘AA20329’, ‘AA20326’) AND DATE BETWEEN ‘2022-08-09 00:00:00’ AND ‘2022-08-12 00:00:00’
ORDER BY LIST, DATE;

2、获取同一个list相邻2行kwh的差值
SELECT r1.date, r1.LIST, r1.id 上一行的id, r1.kwh 上一行的值, r2.id 下一行的id, r2.kwh 下一行的值, r2.kwh - r1.kwh 相邻行的差
from (select (@rownum := @rownum + 1) as rownum, info.LIST, info.kwh, info.date, info.id
FROM (SELECT id, DATE, LIST, kwh
FROM power
WHERE LIST IN (‘AA20300’, ‘AA20315’, ‘AA20328’, ‘AA20329’, ‘AA20326’) AND DATE BETWEEN ‘2022-08-09 00:00:00’ AND ‘2022-08-12 00:00:00’
ORDER BY LIST, DATE) info,
(select @rownum := 0) r
where info.kwh is not NULL
order by info.LIST) r1
left JOIN
(select (@index := @index + 1) as rownum, info.LIST, info.kwh, info.date, info.id
FROM (SELECT id, DATE, LIST, kwh
FROM power
WHERE LIST IN (‘AA20300’, ‘AA20315’, ‘AA20328’, ‘AA20329’, ‘AA20326’) AND DATE BETWEEN ‘2022-08-09 00:00:00’ AND ‘2022-08-12 00:00:00’
ORDER BY LIST, DATE) info,
(select @index := 0) r
where info.kwh is not null
order by info.LIST) r2
on r1.LIST = r2.LIST and r1.rownum = r2.rownum - 1;