求一个每小时用电量的sql语句

[山娃马小三儿]

数据库结构如下
ID Time Value
1 201705310100 10
2 201705310300 16
3 201705310200 14
4 201705310400 19


value代表的是电表读数，求每小时的用电量，返回
201705310100 0
201705310200 4
201705310300 2
201705310400 5

求sql语句或者方法，谢谢。

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([ID] int,[Time] NVARCHAR(100),[Value] int)
Insert #T
select 1,'2017-05-31 01:00',10 union all
select 2,'2017-05-31 03:00',16 union all
select 3,'2017-05-31 02:00',14 union all
select 4,'2017-05-31 04:00',19
Go
--测试数据结束
SELECT  a.Time ,
        ISNULL(a.Value - b.Value,0) AS Value
FROM    #T a
        LEFT JOIN #T b ON DATEDIFF(HOUR, CONVERT(DATETIME, b.Time),
                                   CONVERT(DATETIME, a.Time)) = 1

[道素]

如果你的数据库低于2012，参考楼上，如果高于2012还可以使用LAG，如下

SELECT *,value-ISNULL(LAG(Value)OVER(ORDER BY Time),Value) FROM #t

ID          Time                                                                                                 Value       
----------- ---------------------------------------------------------------------------------------------------- ----------- -----------
1           2017-05-31 01:00                                                                                     10          0
3           2017-05-31 02:00                                                                                     14          4
2           2017-05-31 03:00                                                                                     16          2
4           2017-05-31 04:00                                                                                     19          3

[zhouyuehai1978]

引用 1 楼 sinat_28984567 的回复:

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([ID] int,[Time] NVARCHAR(100),[Value] int)
Insert #T
select 1,'2017-05-31 01:00',10 union all
select 2,'2017-05-31 03:00',16 union all
select 3,'2017-05-31 02:00',14 union all
select 4,'2017-05-31 04:00',19
Go
--测试数据结束
SELECT  a.Time ,
        ISNULL(a.Value - b.Value,0) AS Value
FROM    #T a
        LEFT JOIN #T b ON DATEDIFF(HOUR, CONVERT(DATETIME, b.Time),
                                   CONVERT(DATETIME, a.Time)) = 1

应该给他按时间排个序吧，另外如果并不是每个小时都有数据的话，这样写就不对了。

[二月十六]

引用 3 楼 zhouyuehai1978 的回复:

[quote=引用 1 楼 sinat_28984567 的回复:]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([ID] int,[Time] NVARCHAR(100),[Value] int)
Insert #T
select 1,'2017-05-31 01:00',10 union all
select 2,'2017-05-31 03:00',16 union all
select 3,'2017-05-31 02:00',14 union all
select 4,'2017-05-31 04:00',19
Go
--测试数据结束
SELECT  a.Time ,
        ISNULL(a.Value - b.Value,0) AS Value
FROM    #T a
        LEFT JOIN #T b ON DATEDIFF(HOUR, CONVERT(DATETIME, b.Time),
                                   CONVERT(DATETIME, a.Time)) = 1

应该给他按时间排个序吧，另外如果并不是每个小时都有数据的话，这样写就不对了。[/quote] 恩是应该加一个排序。如果不是每小时都有数据就得改了。

--测试数据
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([ID] int,[Time] NVARCHAR(100),[Value] int)
Insert #T
select 1,'2017-05-31 01:00',10 union all
select 2,'2017-05-31 03:00',16 union all
select 3,'2017-05-31 02:00',14 union all
select 4,'2017-05-31 04:00',19
Go
--测试数据结束
SELECT  a.Time ,
        ISNULL(a.Value - b.Value,0) AS Value
FROM    #T a
        LEFT JOIN #T b ON DATEDIFF(HOUR, CONVERT(DATETIME, b.Time),
                                   CONVERT(DATETIME, a.Time)) = 1
								   ORDER BY a.Time

[等待戈多12]

if not object_id('tb1') is null
    drop table tb1
go
create table tb1(id int, time datetime, value int)
go
--电表读数应该有一个初始值，所以这里多加了一条记录，不然第一条记录统计不到用电量
insert into tb1(id, time, value)
select 5, '2017-05-31 00:00', 10 union all
select 1, '2017-05-31 01:00', 10 union all
select 2, '2017-05-31 03:00', 16 union all
select 3, '2017-05-31 02:00', 14 union all
select 4, '2017-05-31 04:00', 19

--考虑到电表抄表可能1小时不只一条记录，或者某些小时没有抄表记录，所以拿用电量来累计
--按抄表时间重新排序并计算每段时间的用电量，分组汇总
--实际情况会有多个电表，你自己再加一个统计条件
;with ctetb( idnew, id, time ,value)
as
(
	select row_number() over(order by time) as idnew, id, time , value
	from tb1
)

select convert(varchar(13) , time2, 121) counttime, sum(amount) amount
from (
	select 
		c.idnew idnew1, d.idnew idnew2, c.time  time1, d.time time2 ,c.value value1, d.value value2, (d.value-c.value) as amount
	from 
		ctetb c 
	join ctetb d 
	on c.idnew=d.idnew-1
	) a
group by convert(varchar(13) , time2, 121)
order by convert(varchar(13) , time2, 121)

[a350062174]

with a_tmp as (
select time,value,row_number( ) over ( order by time) num  from a_test --a_test 是数据表
) 
select c1.time, isnull(c1.value -c2.value,0) from a_tmp c1 left join a_tmp c2 on c1.num = c2.num+1

order by c1.time