你应该和一个 ON 子句并使用有效语法 GROUP BY 以及 ORDER BY :

select s1.gender, s1.day, sum(s2.score_points) total
from scores s1 inner join scores s2
on s2.gender = s1.gender and s2.day <= s1.day
group by s1.gender, s1.day
order by s1.gender, s1.day

请看演示。
结果：

| gender | day        | total |
| ------ | -----------| ----- |
| F      | 2019-12-30 | 17    |
| F      | 2019-12-31 | 40    |
| F      | 2020-01-01 | 57    |
| F      | 2020-01-07 | 80    |
| M      | 2019-12-18 | 2     |
| M      | 2019-12-25 | 13    |
| M      | 2019-12-30 | 26    |
| M      | 2019-12-31 | 29    |
| M      | 2020-01-07 | 36    |

AND 是返回真/假（1/0）的布尔运算。您似乎想要条件聚合：

SELECT s.day, 
       SUM(CASE WHEN s.gender = 'M' THEN s.score_points ELSE 0 END) AS total_male,
       SUM(CASE WHEN s.gender = 'F' THEN s.score_points ELSE 0 END) AS total_female
FROM Scores s
GROUP BY s.day
ORDER BY s.day;

这个 GROUP BY 键决定结果集的外观。每一组唯一值在结果集中都有一行，其余的列汇总以适合该行。
在这种情况下，您希望每天有一行，因此这就是 GROUP BY .
对于累计金额，使用窗口函数：

SELECT s.day, 
       SUM(CASE WHEN s.gender = 'M' THEN s.score_points ELSE 0 END) AS total_male,
       SUM(CASE WHEN s.gender = 'F' THEN s.score_points ELSE 0 END) AS total_female,
       SUM(SUM(CASE WHEN s.gender = 'M' THEN s.score_points ELSE 0 END)) OVER (ORDER BY s.day) AS running_male,
       SUM(CASE WHEN s.gender = 'F' THEN s.score_points ELSE 0 END) OVER (ORDER BY s.day) AS running_female
FROM Scores s
GROUP BY s.day
ORDER BY s.day;

我不确定这是否有效，但它会通过：

SELECT T1.gender, T1.day, SUM(T2.score_points) AS total
FROM Scores T1
JOIN Scores T2
WHERE T1.gender = T2.gender AND T2.day <= T1.day
GROUP BY T1.gender, t1.day
ORDER BY t1.gender, t1.day;

参考文献

有关更多详细信息，请参见讨论板。有许多公认的解决方案，有各种语言和解释，有效的算法，以及渐近时间/空间复杂性分析1，2。