sql 语句实现计算电量

create table tb(DD decimal(18,1),date_time datetime) insert into tb values(23.0 ,'2011-01-01') insert into tb values(45.0 ,'2011-01-02') insert into tb values(10.0 ,'2011-01-03') insert into tb values(35.0 ,'2011-01-04') go select t.* , 电递增加数 = dd - (select top 1 dd from tb where date_time < t.date_time order by date_time) from tb t drop table tb /* DD date_time 电递增加数 -------------------- ------------------------------------------------------ --------------------- 23.0 2011-01-01 00:00:00.000 NULL 45.0 2011-01-02 00:00:00.000 22.0 10.0 2011-01-03 00:00:00.000 -13.0 35.0 2011-01-04 00:00:00.000 12.0 （所影响的行数为 4 行） */

create table tb(DD decimal(18,1),date_time datetime) insert into tb values(23.0 ,'2011-01-01') insert into tb values(45.0 ,'2011-01-02') insert into tb values(10.0 ,'2011-01-03') insert into tb values(35.0 ,'2011-01-04') go select t.* , 电递增加数 = case when isnull(dd - (select top 1 dd from tb where date_time < t.date_time order by date_time),0) < 0 then 0 else isnull(dd - (select top 1 dd from tb where date_time < t.date_time order by date_time),0) end from tb t drop table tb /* DD date_time 电递增加数 -------------------- ------------------------------------------------------ --------------------- 23.0 2011-01-01 00:00:00.000 .0 45.0 2011-01-02 00:00:00.000 22.0 10.0 2011-01-03 00:00:00.000 .0 35.0 2011-01-04 00:00:00.000 12.0 （所影响的行数为 4 行） */

create table tb(DD decimal(18,1),date_time datetime) insert into tb values(23.0 ,'2011-01-01') insert into tb values(45.0 ,'2011-01-02') insert into tb values(10.0 ,'2011-01-03') insert into tb values(35.0 ,'2011-01-04') go select t.* , 电递增加数 = case when isnull(dd - (select top 1 dd from tb where date_time < t.date_time order by date_time),0) < 0 then dd else isnull(dd - (select top 1 dd from tb where date_time < t.date_time order by date_time),0) end from tb t drop table tb /* DD date_time 电递增加数 -------------------- ------------------------------------------------------ --------------------- 23.0 2011-01-01 00:00:00.000 .0 45.0 2011-01-02 00:00:00.000 22.0 10.0 2011-01-03 00:00:00.000 10.0 35.0 2011-01-04 00:00:00.000 12.0 （所影响的行数为 4 行） */

create table tb(DD decimal(18,1),date_time datetime) insert into tb values(23.0 ,'2011-01-01') insert into tb values(45.0 ,'2011-01-02') insert into tb values(10.0 ,'2011-01-03') insert into tb values(35.0 ,'2011-01-04') go select t.* , 电递增加数 = case when isnull(dd - (select top 1 dd from tb where date_time < t.date_time order by date_time desc),0) < 0 then dd else isnull(dd - (select top 1 dd from tb where date_time < t.date_time order by date_time desc),0) end from tb t drop table tb /* DD date_time 电递增加数 -------------------- ------------------------------------------------------ --------------------- 23.0 2011-01-01 00:00:00.000 .0 45.0 2011-01-02 00:00:00.000 22.0 10.0 2011-01-03 00:00:00.000 10.0 35.0 2011-01-04 00:00:00.000 25.0 （所影响的行数为 4 行） */