SQL查询问题：求授课教师、科目及对应科目最高分并排序

我来帮你排查下问题所在，你的查询逻辑里有个关键错误，导致不符合需求，所以没通过第二个测试用例~

原代码的问题分析 #

你写的窗口函数里用了partition by sb.staff_id，这会计算每个教师所有教授科目的最高分，而不是需求里要求的每个科目自己的最高分。举个例子：如果一个老师同时教数学和英语，数学最高分95，英语最高分88，你的代码会把这两个科目对应的最高分都显示成95，这显然和需求不符。另外distinct在这里的存在也有点多余，甚至可能在某些场景下导致数据异常。

两种正确的解决方案 #

方案1：使用分组聚合（更直接高效） #

这种方式逻辑清晰，直接按教师和科目分组，计算每个科目的最高分：

SELECT 
    st.staff_name,
    sb.subject_name,
    MAX(m.value) AS max_mark
FROM subject sb
INNER JOIN staff st ON st.staff_id = sb.staff_id
INNER JOIN mark m ON m.subject_id = sb.subject_id
GROUP BY st.staff_name, sb.subject_name, sb.subject_id -- 加上subject_id避免同名科目冲突
ORDER BY max_mark DESC;

这里加上sb.subject_id是为了防止出现不同科目同名的情况，确保分组的准确性。

方案2：修正窗口函数的分区字段 #

如果你更倾向用窗口函数，只需要把分区字段从staff_id改成subject_id，同时保留distinct来去重（因为窗口函数会给每个分数行都带上对应科目的最高分，去重后得到每个科目唯一的一行）：

SELECT DISTINCT
    st.staff_name,
    sb.subject_name,
    MAX(m.value) OVER (PARTITION BY sb.subject_id) AS max_mark
FROM subject sb
INNER JOIN staff st ON st.staff_id = sb.staff_id
INNER JOIN mark m ON m.subject_id = sb.subject_id
ORDER BY max_mark DESC;

这两种方案都能准确返回每个科目对应的教师姓名、科目名称和该科目的最高分，并且按最高分降序排序，应该能通过所有测试用例。

内容的提问来源于stack exchange，提问作者Prashant Yallatti