mysql - 计算值之间的列中的减法

Question

我有一个带有数值列的表。我想测量最新价格与前一个价格相差 8 点的次数，例如...

First I subtract 1 from  2    166-158=8    I  want this to count             
Then  I subtract 2 from  3    158-143=15   I dont want this to count           
Then  I subtract 3 from 4     143-140=3    Neither this     
Then  I subtract 4 from 5     140-132=8    I want his to count 

所以sql代码必须返回2个计数

标签： mysqlsql

Answer 1

使用lag()：

select count(*)
from (select t.*, lag(price) over (order by id) as prev_price
      from t
     ) t
where price = prev_price - 8;