mysql - 计算SQL中以下记录之间的距离

Question

我有一个返回这些记录的 View X:

ID   Value
1    8
2    20
3    24
4    80

我需要计算每个记录值和他的跟随值之间的距离。在最后一条记录中，(id = 4) 我应该插入默认距离 100。所以我做了这个查询:

SELECT a.ID , (a.Value-b.Value) as DistanceToNextRecord
from X as a cross join X as b ON ( a.ID = b.ID-1) 

得到了:

ID  DistanceToNextRecord
1   12
2   4
3   56

但我不知道如何处理最后一条记录。我需要另一行:

4   100

我该怎么做？

谢谢!!!

Answer 1

我更喜欢使用相关子查询进行此类计算(除非您的数据库支持 lead():

select x.id, x.value,
       (select x2.value
        from x x2
        where x2.id > x.id
        order by x2.id
        limit 1
       ) - x.value as DistanceToNextRecord
from x x;

这将为最后一行返回 NULL。

编辑:

如果您可以确定 id 是连续的且没有间隙，那么您可以修改您的查询以使用 left outer join:

SELECT a.ID , (a.Value-b.Value) as DistanceToNextRecord
from X a left join
     X b
    ON a.ID = b.ID - 1;