22,301
社区成员




DECLARE @t TABLE(时间 DATETIME,累计量 INT)
INSERT @t(时间, 累计量)
VALUES('2020-02-23 23:59:59',10),('2020-02-24 00:59:59',100),
('2020-02-24 01:30:00',200),('2020-02-24 01:59:59',300),
('2020-02-24 02:30:00',340),('2020-02-24 02:59:59',400),
('2020-02-24 03:30:00',440),('2020-02-24 03:59:59',500),
('2020-02-24 04:30:00',540),('2020-02-24 04:59:59',600),
('2020-02-24 05:30:00',640),('2020-02-24 05:59:59',700),
('2020-02-24 06:30:00',840),('2020-02-24 06:59:59',900)
;WITH t AS (SELECT * FROM @t WHERE 时间='2020-02-24 00:59:59'
UNION ALL SELECT a.* FROM @t a INNER JOIN t ON DATEADD(HOUR,1,t.时间) = a.时间 WHERE a.时间<'2020-2-25')
SELECT *,t.累计量-LAG(t.累计量) OVER (ORDER BY t.时间) 增量 FROM t
DECLARE @t TABLE(时间 DATETIME,累计量 INT)
INSERT @t(时间, 累计量)
VALUES('2020-02-24 00:00:00',100),
('2020-02-24 00:30:00',200),
('2020-02-24 01:00:00',300),
('2020-02-24 01:30:00',340),
('2020-02-24 02:00:00',400),
('2020-02-24 03:00:00',3000)
;WITH t AS (SELECT * FROM @t WHERE 时间='2020-02-24 00:00:00'
UNION ALL SELECT a.* FROM @t a INNER JOIN t ON DATEADD(HOUR,1,t.时间) = a.时间)
SELECT *,t.累计量-LAG(t.累计量) OVER (ORDER BY t.时间) 增量 FROM t
DECLARE @t TABLE(时间 DATETIME,累计量 INT)
INSERT @t(时间, 累计量)
VALUES('2020-02-24 00:00:00',100),
('2020-02-24 01:00:00',300),
('2020-02-24 02:00:00',400),
('2020-02-24 03:00:00',3000)
;WITH t AS (SELECT * FROM @t WHERE 时间='2020-02-24 00:00:00'
UNION ALL SELECT a.* FROM @t a INNER JOIN t ON DATEADD(HOUR,1,t.时间) = a.时间)
SELECT *,LAG(t.累计量) OVER (ORDER BY t.时间) 增量 FROM t