需求就是统计电表每天用了多少电
电表记录表（id 自增编号 dianbiao 电表 curvalue 当前电表值 addtime 抄表时间）
id dianbiao curvalue addtime
1 dianbiao1 175.43 2015-06-15 12:17:25
2 dianbiao2 207.23 2015-06-15 12:17:25
3 dianbiao2 219.29 2015-06-15 14:17:37
4 dianbiao1 195.21 2015-06-15 14:17:34
4 dianbiao1 225.43 2015-06-16 12:17:25
6 dianbiao1 255.53 2015-06-16 14:17:34
7 dianbiao3 175.43 2015-06-17 12:17:25
统计结果如下（逻辑：某表 用当天 最后电表值 减去 最开始电表值，如果当天某表只有一条记录，用电量则为0）
统计时间 电表 用电
2015-06-15 dianbiao1 19.78
2015-06-15 dianbiao2 12.06
2015-06-16 dianbiao1 30.10
2015-06-17 dianbiao3 0
电表数可能不止以上三个 ，所以sql得动态 ..
------解决思路----------------------
;WITH CTE AS(
SELECT id,dianbiao,curvalue,addtime
,CONVERT(VARCHAR(10),[addtime],120)addtime1
,ROW_NUMBER()OVER(PARTITION BY dianbiao,CONVERT(VARCHAR(10),[addtime],120) ORDER BY [addtime])RN
FROM TB
)
SELECT T1.addtime1 AS[统计时间]
,T1.dianbiao AS[电表]
,SUM(ISNULL(T2.[curvalue]-T1.[curvalue],0))AS[用电]
FROM CTE T1
LEFT JOIN CTE T2
ON T1.dianbiao=T2.dianbiao
AND T1.addtime1=T2.addtime1
AND T1.RN+1=T2.RN
GROUP BY T1.addtime1
,T1.dianbiao
------解决思路----------------------
开窗函数
DECLARE @t_TB TABLE ([id] tinyint,[dianbiao] NVARCHAR(10),[curvalue] numeric(10,2),[addtime] DATETIME);
INSERT INTO @t_TB VALUES
('1','dianbiao1','175.43','2015-06-15 12:17:25'),
('2','dianbiao2','207.23','2015-06-15 12:17:25'),
('3','dianbiao2','219.29','2015-06-15 14:17:37'),
('4','dianbiao1','195.21','2015-06-15 14:17:34'),
('4','dianbiao1','225.43','2015-06-16 12:17:25'),
('6','dianbiao1','255.53','2015-06-16 14:17:34'),
('7','dianbiao3','175.43','2015-06-17 12:17:25');
;with cte
as
(
SELECT convert(varchar(10),addtime,120) addtime, dianbiao,
max([curvalue])over(PARTITION by convert(varchar(10),addtime,120),dianbiao order by [addtime])
-min([curvalue])over(PARTITION by convert(varchar(10),addtime,120),dianbiao order by [addtime]) as usage
FROM @t_TB
)
select * from cte where usage >0
/*
addtime dianbiao usage
---------- ---------- ---------------------------------------
2015-06-15 dianbiao1 19.78
2015-06-15 dianbiao2 12.06
2015-06-16 dianbiao1 30.10
*/