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