求一个mssql存储过程或查询语句
表结构：
时间， 电表编号，度数，倍率
2013-9-11 04:50，0001，100，10
2013-9-11 03:30，0001，80，10
2013-9-11 05:30, 0001, 105,10
2013-9-12 010:50，0001，120，10
2013-9-13 09:30，0001，180，10
2013-9-14 10:30, 0001, 205,10
2013-9-11 04:50，0002，100，10
2013-9-11 03:30，0002，80，10
2013-9-11 05:30, 0003, 105,10
2013-9-12 010:50，0003，120，10
2013-9-13 09:30，0003，180，10
2013-9-14 10:30, 0003, 205,10
2013-9-11 05:30, 0005, 105,10
2013-9-12 010:50，0005，120，10
2013-9-13 09:30，0005，180，10
2013-9-14 10:30, 0005, 205,10
怎么求出一段时间内每个电表编号的使用度数。
使用度数为选中时间段内的最后一个时间度数减去
第一个时间度数。
如果查询时间段位：9-1到10-1
则结果为
电表编号 使用度数
0001 105
0002 20
0003 100
0005 100
求这个存储过程或查询怎么写？
[解决办法]
create table #tab(时间 datetime,电表编号 varchar(50),度数 int ,倍率 int)
insert into #tab
select '2013-9-11 04:50',0001,100,10 union all
select '2013-9-11 03:30',0001,80,10 union all
select '2013-9-11 05:30', 0001,105,10 union all
select '2013-9-12 010:50',0001,120,10 union all
select '2013-9-13 09:30',0001,180,10 union all
select '2013-9-14 10:30', 0001, 205,10 union all
select '2013-9-11 04:50',0002,100,10 union all
select '2013-9-11 03:30',0002,80,10 union all
select '2013-9-11 05:30',0003,105,10 union all
select '2013-9-12 010:50',0003,120,10 union all
select '2013-9-13 09:30',0003,180,10 union all
select '2013-9-14 10:30',0003, 205,10 union all
select '2013-9-11 05:30',0005, 105,10 union all
select '2013-9-12 010:50',0005,120,10 union all
select '2013-9-13 09:30',0005,180,10 union all
select '2013-9-14 10:30', 0005, 205,10
select * from #tab
if OBJECT_ID('sp_test','p')is not null
drop proc sp_test
go
create proc sp_test
@starttime datetime,
@endtime datetime
as
begin
select 电表编号,sum(度数)度数 from #tab
where 时间 between @starttime and @endtime
group by 电表编号
end
go
exec sp_test'2013-9-1','2013-10-1'
----------------------------------------
电表编号 度数
-------------------------------------------------- -----------
1 790
2 180
3 610
5 610
(4 行受影响)
create table wg
(时间 varchar(20), 电表编号 varchar(10), 度数 int,倍率 int)
insert into wg
select '2013-9-11 04:50', '0001', 100,10 union all
select '2013-9-11 03:30', '0001', 80,10 union all
select '2013-9-11 05:30', '0001', 105,10 union all
select '2013-9-12 010:50', '0001', 120,10 union all
select '2013-9-13 09:30', '0001', 180,10 union all
select '2013-9-14 10:30', '0001', 205,10 union all
select '2013-9-11 04:50', '0002', 100,10 union all
select '2013-9-11 03:30', '0002', 80,10 union all
select '2013-9-11 05:30', '0003', 105,10 union all
select '2013-9-12 010:50', '0003', 120,10 union all
select '2013-9-13 09:30', '0003', 180,10 union all
select '2013-9-14 10:30', '0003', 205,10 union all
select '2013-9-11 05:30', '0005', 105,10 union all
select '2013-9-12 010:50', '0005', 120,10 union all
select '2013-9-13 09:30', '0005', 180,10 union all
select '2013-9-14 10:30', '0005', 205,10
-- 方法1
select 电表编号,max(度数)-min(度数) '使用度数'
from wg
where cast(时间 as datetime)
between cast('2013-09-01 00:00' as datetime)
and cast('2013-10-01 00:00' as datetime)
group by 电表编号
/*
电表编号 使用度数
---------- -----------
0001 125
0002 20
0003 100
0005 100
(4 row(s) affected)
*/
-- 方法2
with t as
(select 电表编号,度数,
row_number() over(partition by 电表编号 order by cast(时间 as datetime)) 'rn'
from wg
where cast(时间 as datetime)
between cast('2013-09-01 00:00' as datetime)
and cast('2013-10-01 00:00' as datetime)
)
select a.电表编号,
(select 度数 from t b where b.电表编号=a.电表编号 and b.rn=max(a.rn))
-(select 度数 from t b where b.电表编号=a.电表编号 and b.rn=1) '使用度数'
from t a
group by a.电表编号
/*
电表编号 使用度数
---------- -----------
0001 125
0002 20
0003 100
0005 100
(4 row(s) affected)
*/
;with cte(时间,电表编号,度数,倍率) as
(
select '2013-9-11 04:50','0001',100,10
union all select '2013-9-11 03:30','0001',80,10
union all select '2013-9-11 05:30','0001',105,10
union all select '2013-9-12 010:50','0001',120,10
union all select '2013-9-13 09:30','0001',180,10
union all select '2013-9-14 10:30','0001',205,10
union all select '2013-9-11 04:50','0002',100,10
union all select '2013-9-11 03:30','0002',80,10
union all select '2013-9-11 05:30','0003',105,10
union all select '2013-9-12 010:50','0003',120,10
union all select '2013-9-13 09:30','0003',180,10
union all select '2013-9-14 10:30','0003',205,10
union all select '2013-9-11 05:30','0005',105,10
union all select '2013-9-12 010:50','0005',120,10
union all select '2013-9-13 09:30','0005',180,10
union all select '2013-9-14 10:30','0005',205,10
)
select a.电表编号,a.度数-b.度数 as 度数
from (select *,rn=ROW_NUMBER() over(PARTITION by 电表编号 order by 时间 desc) from cte)a
left join (select *,rn=ROW_NUMBER() over(PARTITION by 电表编号 order by 时间 ) from cte)b
on a.电表编号=b.电表编号
where a.rn=1 and b.rn=1
/*
电表编号度数
0001125
000220
0003100
0005100
*/
