表
dati,a8,a10
2008-8-1,10,12
2008-8-2,11,13
2008-8-3,15,8
2008-8-4,8,4
计算表中的第一条记录与最后一条记录的a8,a10的差值
结果为
a8,a10
2,8能否用一条sql语句搞定?
dati,a8,a10
2008-8-1,10,12
2008-8-2,11,13
2008-8-3,15,8
2008-8-4,8,4
计算表中的第一条记录与最后一条记录的a8,a10的差值
结果为
a8,a10
2,8能否用一条sql语句搞定?
A10=MAX(A10)-MIN(A10)
FROM TB
from (select a8 ,a10 from tb where dati=(select max(dati) from tb) ) a , (select a8 ,a10 from tb where dati=(select min(dati) from tb)) b
select a8=max(a.a8)-min(a.a8),a10=max(a.a10)-min(a.a10) from
(select top 2 a8,a10 from 表 order by a8) a
if object_id('表') is not null
drop table 表
create table 表
( dati datetime ,
a8 int ,
a10 int
)
insert into 表 select '2008-8-1',10,12
union all select '2008-8-2',11,13
union all select '2008-8-3',15,8
union all select '2008-8-4',8,4
select a8=max(a.a8)-min(a.a8),a10=max(a.a10)-min(a.a10) from
(select top 2 a8,a10 from 表 order by a8) a
if object_id('t1') is not null
drop table t1
create table t1
( dati datetime ,
a8 int ,
a10 int
)
insert into t1 select '2008-8-1',10,12
union all select '2008-8-2',11,13
union all select '2008-8-3',15,8
union all select '2008-8-4',8,4
select abs(sum(a8)) a1,abs(sum(a10)) a2 from (
select a8,a10 from t1 where dati <=all(select dati from t1)
union all
select -a8 a8,-a10 a10 from t1 where dati >=all(select dati from t1)
)t2
/*
a1 a2
----------- -----------
2 8(所影响的行数为 1 行)*/
select abs(sum(a8)) a8,abs(sum(a10)) a10 from (
select top 1 a8,a10 from t1 where dati <=all(select dati from t1)
union all
select top 1 -a8 a8,-a10 a10 from t1 where dati >=all(select dati from t1)
)t2
and b.dati=(select min(dati) from t)