mysql计算答案放入空列

Question

我想使用sql代码将每分钟价格之间的差异(Diff)放入mysql表中的空列中。我E价格每分钟都在变化。之前:

Date,    Time,   Price,  Diff  
20140101,230300, 1.3761, NULL
20140101,230400, 1.3762, NULL

sql代码运行后:

Date,    Time,   Price,  Diff  
20140101,230300, 1.3761, 0.0000
20140101,230400, 1.3762, 0.0001

Answer 1

可能没有真正的理由将差异放入表中。您可以通过简单的自连接查询获得差异

大约:

select ta.date, ta.time, ta.price, diff as ta.price-tb.price
from mytable ta join mytable tb 
where tb.time = ta.time-100;

关于将日期时间作为单个实体的注释将使表的时间差计算变得更加容易，因为有诸如 TIMEDIFF 之类的函数可以提供帮助。