时间戳

首页 > 解决方案 > SQL Server：从第一行减去最后一行

sql - SQL Server：从第一行减去最后一行

问题描述

我有一个返回很多行的查询。我需要最后一行和第一行中的值之间的差异。TimeCol是一DateTime列，所有其他列的类型都是float.

我当前的（简化的）查询是这样的：

select 
    timeCol, TotSoap_W1, TotSoap_W2, TotEnergy
from
    TotEnergy
where 
    TimeCol >= '2019-03-01 00:00:00' and timeCol < '2019-03-02 00:00:00'
order by 
    TimeCol asc

标签： sqlsql-serverdifference

解决方案

这可能不是最好的方法，但应该完成这项工作：

SELECT B.TotSoap_W1-A.TotSoap_W1 as TotSoap_W1, B.TotSoap_W2-A.TotSoap_W2 as TotSoap_W2, B.TotEnergy-A.TotEnergy as TotEnergy
FROM
(select TOP(1) timeCol, TotSoap_W1, TotSoap_W2, TotEnergy
from TotEnergy
where TimeCol >= '2019-03-01 00:00:00' and timeCol < '2019-03-02 00:00:00'
order by TimeCol ASC) A,
(select TOP(1) timeCol, TotSoap_W1, TotSoap_W2, TotEnergy
from TotEnergy
where TimeCol >= '2019-03-01 00:00:00' and timeCol < '2019-03-02 00:00:00'
order by TimeCol DESC
) B

推荐阅读