假设我们有以下简单的架构和数据:
DROP TABLE #builds
CREATE TABLE #builds (
Id INT IDENTITY(1,1) NOT NULL,
StartTime INT,
IsPassed BIT
)
INSERT INTO #builds (StartTime, IsPassed) VALUES
(1, 1),
(7, 1),
(10, 0),
(15, 1),
(21, 1),
(26, 0),
(34, 0),
(44, 0),
(51, 1),
(60, 1)
SELECT StartTime, IsPassed, NextStartTime,
CASE IsPassed WHEN 1 THEN 0 ELSE NextStartTime - StartTime END Duration
FROM (
SELECT
LEAD(StartTime) OVER (ORDER BY StartTime) NextStartTime,
StartTime, IsPassed
FROM #builds
) x
ORDER BY StartTime
它产生以下结果集:
StartTime IsPassed NextStartTime Duration
1 1 7 0
7 1 10 0
10 0 15 5
15 1 21 0
21 1 26 0
26 0 34 8
34 0 44 10
44 0 51 7
51 1 60 0
60 1 NULL 0
我需要总结非零连续的Duration值,并在批处理中第一行的StartTime处显示它们。即我需要做到这一点:
StartTime Duration
10 5
26 25
我只是不知道该怎么做。
PS:当然,实际表包含更多行。