两个SQL语句的差值
一段时间之内，最早时间的值与最晚时间的值得差
select value,max(time)-min(time) from D_live_data where field_id=156 group by value
select t1.value-t2.value
from
(select top1 value from D_live_data where field_id=156 order by time) t1,
(select top1 value from D_live_data where field_id=156 order by time desc) t2
Sql如何数据库求相邻两行的差值？
现有表table
----------------------------------------------------------
年 月 日 气温
2009 1 1 6.8
2009 1 2 6.1
2009 1 3 5.6
2009 1 4 4.7
2009 1 5 4
2009 1 6 3
2009 1 7 4.5
2009 1 8 8.9
2009 1 9 11.2
2009 1 10 8.7
---------------------------------------------------------
通过运算求相邻两列行中“气温”的差值，即得到以下表：
----------------------------------------------------------
年 月 日 气温 与昨日温差
2009 1 1 6.8
2009 1 2 6.1 -0.7
2009 1 3 5.6 -0.5
2009 1 4 4.7 -0.9
2009 1 5 4 -0.7
2009 1 6 3 -1
2009 1 7 4.5 1.5
2009 1 8 8.9 4.4
2009 1 9 11.2 2.3
2009 1 10 8.7 -2.5
---------------------------------------------------------
WITH test (年,月,日,气温) AS (
SELECT 2009,1,1,6.8 UNION ALL
SELECT 2009,1,2,6.1 UNION ALL
SELECT 2009,1,3,5.6 UNION ALL
SELECT 2009,1,4,4.7 UNION ALL
SELECT 2009,1,5,4 UNION ALL
SELECT 2009,1,6,3 UNION ALL
SELECT 2009,1,7,4.5 UNION ALL
SELECT 2009,1,8,8.9 UNION ALL
SELECT 2009,1,9,11.2 UNION ALL
SELECT 2009,1,10,8.7
)
SELECT t1.*,
t1.气温 - t0.气温 与昨日温差
FROM test t1
LEFT JOIN test t0
ON DATEDIFF(day,
DateAdd(day,t0.日-1,DateAdd(month,t0.月-1,DateAdd(year,t0.年-1900,'1900-01-01'))),
DateAdd(day,t1.日-1,DateAdd(month,t1.月-1,DateAdd(year,t1.年-1900,'1900-01-01')))
) = 1
WITH test (年,月,日,气温) AS (
SELECT 2009,1,1,6.8 UNION ALL
SELECT 2009,1,2,6.1 UNION ALL
SELECT 2009,1,3,5.6 UNION ALL
SELECT 2009,1,4,4.7 UNION ALL
SELECT 2009,1,5,4 UNION ALL
SELECT 2009,1,6,3 UNION ALL
SELECT 2009,1,7,4.5 UNION ALL
SELECT 2009,1,8,8.9 UNION ALL
SELECT 2009,1,9,11.2 UNION ALL
SELECT 2009,1,10,8.7
)
select a.*,b.气温-a.气温 as 与昨日温差
from
(select *,row_number()over(order by getdate()) as id from test) as a
inner join
(select *,row_number()over(order by getdate()) as id from test) as b
on
a.id=b.id-1
用SQL语句直接得出两个记录之间某字段的差值。
现有如下记录：
序号 表号 购电次数 剩余电量 总购电量 总用电量
1 22 1 33 2345 2312
2 22 2 23 3453 3430
3 22 3 243 3998 3755
4 22 4 36 4896 4850
4 22 5 76 5476 5400
通过SQL语句 根据提供的购电次数信息 直接计算出某两次购电之间的用电量，例如第4次购电和第3次购电之间 用电量为 4850-3755 =1095度。
方法一
WITH test (序号,表号,购电次数,剩余电量,总购电量,总用电量) AS (
SELECT 1,22,1,33,2345,2312 UNION ALL
SELECT 2,22,2,23,3453,3430 UNION ALL
SELECT 3,22,3,243,3998,3755 UNION ALL
SELECT 4,22,4,36,4896,4850 UNION ALL
SELECT 4,22,5,76,5476,5400
)
SELECT Sum([总用电量] * ( CASE [购电次数]
WHEN 3 THEN -1
WHEN 4 THEN 1
END ))
FROM test
WHERE [购电次数] = 3 OR [购电次数] = 4
方法二
WITH test (序号,表号,购电次数,剩余电量,总购电量,总用电量) AS (
SELECT 1,22,1,33,2345,2312 UNION ALL
SELECT 2,22,2,23,3453,3430 UNION ALL
SELECT 3,22,3,243,3998,3755 UNION ALL
SELECT 4,22,4,36,4896,4850 UNION ALL
SELECT 4,22,5,76,5476,5400
)
select B.总用电量-A.总用电量 from
(select * from test where 购电次数=3) A,
(select * from test where 购电次数=4) B
where A.表号=B.表号
方法三
WITH test (序号,表号,购电次数,剩余电量,总购电量,总用电量) AS (
SELECT 1,22,1,33,2345,2312 UNION ALL
SELECT 2,22,2,23,3453,3430 UNION ALL
SELECT 3,22,3,243,3998,3755 UNION ALL
SELECT 4,22,4,36,4896,4850 UNION ALL
SELECT 4,22,5,76,5476,5400
)
--全部数据
--SELECT a.*,(a.总用电量 - b.总用电量 ) as 用电量
--from test a left join test b
--on a.购电次数=b.购电次数+1 and a.表号=b.表号
--第4次购电:
SELECT a.*,(a.总用电量 - b.总用电量 ) as 用电量
from test a left join test b
on a.购电次数=b.购电次数+1 and a.表号=b.表号
where a.购电次数=4 and a.表号=22