-- 测试数据 参考 https://www.cnblogs.com/thxj/p/12727589.html
CREATE TABLE `school_score` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(1) DEFAULT NULL,
`course` char(10) DEFAULT NULL,
`score` int (2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ;
CREATE TABLE `school_score` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(1) DEFAULT NULL,
`course` char(10) DEFAULT NULL,
`score` int (2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ;
INSERT INTO `school_score` VALUES (1, 'A','Chinese',80),(2, 'B','Chinese',90),
(3, 'C','Chinese',70),(4, 'A','Math',70),(5, 'B','Math',100),(6, 'C','Math',80),
(7, 'A','English',90),(8, 'B','English',85),(9, 'C','English',99);
-- 按学科分组并且逐行连续累加分数
SELECT
`name`,
`course`,
`score`,
SUM(score) over (PARTITION BY `course` ORDER BY score DESC ) AS course_score_total
FROM
`school_score`;
-- 执行结果
name course score course_score_total
------ ------- ------ --------------------
B Chinese 90 90
A Chinese 80 170
C Chinese 70 240
C English 99 99
A English 90 189
B English 85 274
B Math 100 100
C Math 80 180
A Math 70 250
注意:这里的ORDER BY score很关键,如果没有加ORDER BY结果就是组内SUM,显然这并不是我们想要的,并且ORDER BY的规则不一样每行聚合的结果可能会不一样,但最终组内聚合的结果一样
例如:
SELECT
`name`,
`course`,
`score`,
SUM(score) over (PARTITION BY `course` ORDER BY score ASC/*这里升序*/) AS course_score_total
FROM
`school_score`;
-- 执行结果,注意和上面的不同
name course score course_score_total
------ ------- ------ --------------------
C Chinese 70 70
A Chinese 80 150
B Chinese 90 240
B English 85 85
A English 90 175
C English 99 274
A Math 70 70
C Math 80 150
B Math 100 250