您可以按如下方式使用最大窗口函数：

WITH sum_pt AS
(
    SELECT student, period,
    SUM(point) AS st_period_pt
    FROM table1
    GROUP BY student, period
),
max_sum as
(
  SELECT *,
    MAX(st_period_pt) OVER (PARTITION BY period) AS max_pt_sum
  FROM sum_pt
)
SELECT student, period,  st_period_pt
FROM max_sum
WHERE st_period_pt = max_pt_sum
ORDER BY period

参见demo。

尝试使用窗口函数：

• SUM，以获得每对〈student，period〉的总积分
• ROW_NUMBER，对每个周期的点数进行排序

然后，您可以选择排名= 1，以获得每个时段的最高分。

WITH students_with_total_points AS (
    SELECT *, SUM(point) OVER(PARTITION BY student, period) AS total_points 
    FROM tab
), ranking_on_periods AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY period ORDER BY total_points DESC) AS rn 
    FROM students_with_total_points
)
SELECT id, student, period, total_points
FROM ranking_on_period
WHERE rn = 1

可以按如下方式使用左联接：

select t1.period, t1.student, t1.score
from (
    select student, period, score
    from (
        select student, period, SUM(point) as score 
        from table1 s
        group by student, period
    ) as s
    group by period, student
) as t1
left join (
    select student, period, score
    from (
        select student, period, SUM(point) as score 
        from table1 s
        group by student, period
    ) as s
    group by period, student
) as t2 on t1.student = t2.student and t1.score < t2.score
where t2.score is null;

这个查询还将列出学生和他们的时间段，如果分数为0，您可以通过在t1和t2临时表中添加where close来排除他们。