mysql 两条数据相减_mysql 实现相邻两条数据相减

要计算mysql同列当中，当前数据前去上一个数据的差

查询数据

SELECT

a.`STORE_ID` AS id,

a.`STORE_NAME` AS NAME,

a.`DEPOSIT` AS curr,

@a.DEPOSIT AS pre, //上一条记录的deposit值

@a.DEPOSIT:= a.DEPOSIT AS tmp

FROM stock_store_addinfo a,(SELECT @a.DEPOSIT:=0)s

@a.DEPOSIT:=a.DEPOSIT AS tmp 将a.DEPOSIT值赋到临时临时变量 @a.DEPOSIT

SELECT @a.DEPOSIT:=0 选择当前表,上一条记录 a.DEPOSIT,默认值为0

实现差值

SELECT

b.id,

b.NAME,

b.curr,

b.pre,

(b.curr - b.pre) AS diff

FROM

(

SELECT

a.`STORE_ID` AS id,

a.`STORE_NAME` AS NAME,

a.`DEPOSIT` AS curr,

@a.DEPOSIT AS pre,

@a.DEPOSIT:= a.DEPOSIT

FROM stock_store_addinfo a,(SELECT @a.DEPOSIT:=0)r

)b