主页 / user-254675

SoT's questions

Martin Hope
SoT
Asked: 2022-06-26 21:58:11 +0800 CST
  • -2

鉴于此表：

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)？还是我错过了什么？

sql-server sum