工作總和如何

鑑於此表：

CREATE TABLE Table1
(
 [Classroom] int,
 [CourseName] varchar(8),
 [Lesson] varchar(9),
 [StartTime] char(4),
 [EndTime] char(4)
);

然後：

INSERT INTO Table1
(
 [Classroom], 
 [CourseName], 
 [Lesson], 
 [StartTime], 
 [EndTime]
)
VALUES
   (1001, 'Course 1', 'Lesson 1', '0800', '0900'),
   (1001, 'Course 1', 'Lesson 2', '0900', '1000'),
   (1001, 'Course 1', 'Lesson 3', '1000', '1100'),
   (1001, 'Course 1', 'Lesson 6', '1100', '1200'),
   (1001, 'Course 2', 'Lesson 10', '1100', '1200'),
   (1001, 'Course 2', 'Lesson 11', '1200', '1300'),
   (1001, 'Course 1', 'Lesson 4', '1300', '1400'),
   (1001, 'Course 1', 'Lesson 5', '1400', '1500');

我的查詢是：

With A AS 
(
 SELECT 
   ClassRoom
   CourseName
   StartTime
   EndTime
   PrevCourse = LAG(CourseName, 1, CourseName) OVER (ORDER BY StartTime)
 FROM   Table1
), B AS (
 SELECT 
   ClassRoom
   CourseName
   StartTime
   EndTime
   Ranker = SUM(CASE WHEN CourseName = PrevCourse THEN 0 ELSE 1 END)
               OVER (ORDER BY StartTime, CourseName)
 FROM   A
)
SELECT B.* FROM B;

這給了我以下結果：

ClassRoom CourseName StartTime EndTime Ranker
1001      Course 1   0800   0900    0
1001      Course 1   0900   1000    0
1001      Course 1   1000   1100    0
1001      Course 1   1100   1200    0
1001      Course 2   1100   1200    1
1001      Course 2   1200   1300    1
1001      Course 1   1300   1400    2
1001      Course 1   1400   1500    2

請關注排名列。如果我沒有誤解，在目前課程與上一課程不同的每一行，然後 sum(1); 下一行，目前課程 == 上一課程，然後是 sum(0)，所以我對排名的期望應該是：(0,0,0,0), (1,1), (1,1) 但它給我（0,0,0,0），（1,1），（2,2）。為什麼最後它給了我 (2, 2)？還是我錯過了什麼？

您的求和視窗框架是所有先前的行。這更接近你想要的嗎？

, Ranker = SUM(CASE WHEN CourseName = PrevCourse THEN 0 ELSE 1 END)
              OVER (PARTITION BY CourseName ORDER BY StartTime)

ClassRoom   CourseName  StartTime   EndTime Ranker
1001    Course 1    0800    0900    0
1001    Course 1    0900    1000    0
1001    Course 1    1000    1100    0
1001    Course 1    1100    1200    0
1001    Course 2    1100    1200    1
1001    Course 2    1200    1300    1
1001    Course 1    1300    1400    1
1001    Course 1    1400    1500    1

引用自：https://dba.stackexchange.com/questions/313781