-
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 行) */