34,575
社区成员
发帖
与我相关
我的任务
分享
IF NOT OBJECT_ID(N'Tempdb..#tmp_1') IS NULL DROP TABLE #tmp_1
CREATE TABLE #tmp_1([time] BIGINT,energy INT)
INSERT INTO #tmp_1
SELECT '1451577600','1000' UNION ALL
SELECT '1451577660','1010' UNION ALL
SELECT '1451581200','1050' UNION ALL
SELECT '1451584800','1080' UNION ALL
SELECT '1451588400','1200' UNION ALL
SELECT '1451592000','1400'
SELECT [time] ,lead(energy,1,NULL) OVER(ORDER BY [time])-energy AS energy
FROM (
SELECT CONVERT(CHAR(23),DATEADD(hh,8,DATEADD(s,[time],'19700101')),121) AS [time1],[time],energy
,ROW_NUMBER() OVER (PARTITION BY DATEPART(hh,DATEADD(hh,8,DATEADD(s,[time],'19700101'))) ORDER BY CONVERT(CHAR(23),DATEADD(hh,8,DATEADD(s,[time],'19700101')),121)) AS rno
FROM #tmp_1
) a
WHERE a.rno=1
time energy
-------------------- -----------
1451577600 50
1451581200 30
1451584800 120
1451588400 200
1451592000 NULL