主页 / dba / 问题 / 313781
Accepted
SoT
Asked: 2022-06-26 21:58:11 +0800 CST

工作总和如何

  • 772

鉴于此表：

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

2 个回答

  1. Best Answer

    您的求和窗口框架是所有先前的行。这更接近你想要的吗？

    , 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
    • 1

  2. 我已经改变了你的表 DDL。

    • 我更改了CHAR (4) definition of your start_time andend_time fields toTIME(0)`。我不能过分强调为您的数据拥有正确数据类型的重要性！如何检查时间是否有效？插入“5399”将被接受为有效的“时间”！使用正确的数据类型意味着您可以免费进行内置范围检查！

      试想一下，某天早上出现了公共交通问题！每个人都迟到了一个小时 - 因此，学校校长决定将所有课程重新安排到 1 小时后

    • 我ROW_NUMBER()在第二个 CTE 中添加了一个窗口函数。

    • 我更改了其余 SQL的演示文稿 - 但没有更改内容（下面的所有代码都可以在此处的小提琴中找到）：


    CREATE TABLE 测试 ( class_room INTEGER, course_name VARCHAR (8), 课程 VARCHAR (9), start_time TIME(0), SQL:

    WITH a AS 
(
  SELECT 
    class_room,
    course_name,
    start_time,
    end_time,
    LAG(course_name, 1, 'Course 1') OVER (ORDER BY start_time) AS prev_course
  FROM   test
), b AS 
(
  SELECT 
    class_room,
    course_name,
    start_time,
    end_time,
    SUM
    (
      CASE 
        WHEN course_name = prev_course THEN 0 
        ELSE 1 
      END
    )  OVER (ORDER BY start_time, course_name) AS ranker
  FROM   a
)
SELECT b.* FROM b;

    结果（和结果分析）：

    class_room  course_name start_time  end_time    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
    • 0

相关问题