sql 语句实现计算电量

最新推荐文章于 2024-05-28 19:46:27 发布

原创最新推荐文章于 2024-05-28 19:46:27 发布 · 1.4k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #insert #date #table #null

技术专栏收录该内容

82 篇文章

订阅专栏

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 行）


*/