代码之家 › 专栏 › 技术社区 › SoT

SQL Server中求和函数的工作方式

sum sql-server sql

SoT · 技术社区 · 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

请关注列ranker。如果我没有误解,在当前课程与前一课程不同的每一行,那么求和(1);下一行,其中current course==previous course,然后求和(0),所以我对ranker的期望应该是:(0,0,0,0),(1,1),(1,1),但它给了我(0,0,0,0),(1,1),(2,2)。

为什么最后我得到(2,2)?我错过什么了吗?

2 回复 | 直到 2 年前

forpas 2 年前

表达式:

CASE WHEN CourseName = PrevCourse THEN 0 ELSE 1 END

退货 1 什么时候 CourseName 不同于 PrevCourse .

如果你在里面多加一列,你会看得更清楚 B :

B AS (
  SELECT ClassRoom
       , CourseName
       , StartTime
       , EndTime
       , CASE WHEN CourseName = PrevCourse THEN 0 ELSE 1 END flag -- check this
       , Ranker = SUM(CASE WHEN CourseName = PrevCourse THEN 0 ELSE 1 END)
                OVER (ORDER BY StartTime, CourseName)
  FROM   A
)

如果希望得到的结果为(0,0,0,0),(1,1),(1,1),则应添加 PARTITION BY 内部条款 OVER 对于列 Ranker :

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

请参见 demo .

George Menoutis 2 年前

似乎,因为你有比较不同课程名称的基本逻辑,你认为 sum 我也会这么做。不会的。对于Starttime=1300行 case 表达式在0900到1300的所有7行上求值。1100和1300的大小写都为1,因此总和返回2。