SQL查找两行之间的区别

问题描述

我有一个Informix数据库，其中包含许多不同位置的测量温度值.每15分钟对所有位置进行一次测量，然后将时间戳记加载到同一表中.表格看起来像这样:

I have an Informix database containing measured temperature values for quite a few different locations. The measurements are taken every 15 min for all locations and then loaded with a timestamp into the same table. Table looks like this:

locId    dtg               temp
aaa      2009-02-25 10:00  15
bbb      2009-02-25 10:00  20
ccc      2009-02-25 10:00  24
aaa      2009-02-25 09:45  13
ccc      2009-02-25 09:45  16
bbb      2009-02-25 09:45  18
ddd      2009-02-25 09:45  12
aaa      2009-02-25 09:30  11
ccc      2009-02-25 09:30  14
bbb      2009-02-25 09:30  15
ddd      2009-02-25 09:30  10

现在，我想查询一个查询，该查询为我显示所有站点的最后两次测量之间的温度变化.而且，只有那些具有更新的度量值的度量值.例如，在上表中，位置ddd将不包括在内.结果就是:

Now I would like a query that present me with the change in temperature between the two last measurements for all stations. And also, only the ones that has an updated measurement. For example in the table above, location ddd would not be included.So the result becomes:

locId    change
aaa      2
bbb      2
ccc      8

我尝试了很多，但是找不到任何好的解决方案.实际上，从一个网页询问大约700个位置，所以我认为查询必须相当有效.

I have tried alot but I can´t find any good solution. In reality it is about 700 locations that is asked from a web page so I think the query needs to be fairly efficient.

非常感谢您的帮助！
//Jesper

Would really appreciate some help!
//Jesper

推荐答案

set now = select max(dtg) from table;
set then = select max(dtg) from table where dtg < now;

select locID, old.temp-new.temp from
      table as old join
      table as new
      on old.locId = new.locID
where
      old.dtg = then and
      new.dtg = now;

---

假设所有时间都是正确的

---

assumes that all times will be exact

这篇关于SQL查找两行之间的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！