似乎您正在查找以下查询：

SELECT
    ST.STAFF_NAME,
    SB.SUBJECT_NAME,
    MAX(M.VALUE) AS MAX_MARK
FROM
    STAFF     ST
    JOIN SUBJECT   SB ON SB.STAFF_ID = ST.STAFF_ID
    JOIN MARK      M ON M.SUBJECT_ID = SB.SUBJECT_ID
GROUP BY
    ST.STAFF_ID,
    ST.STAFF_NAME,
    SB.SUBJECT_ID,
    SB.SUBJECT_NAME
ORDER BY
    MAX_MARK DESC;

我认为你想要：

select  
    st.staff_name,
    su.subject_name,
    max(ma.value) as max_mark
from subject su
inner join staff st on st.staff_id   = sb.staff_id 
inner join mark ma  on ma.subject_id = su.subject_id 
group by st.staff_id, st.staff_name, su.subject_id, su.subject_name
order by max_mark desc;

可以使用窗口函数：

select staff_name, subject_name, max_mark
from (select st.staff_name, su.subject_name, ma.value as max_mark,
             row_number() over (partition by st.staff_name order by ma.value desc) as seqnum
      from subject su join
           staff st
           on st.staff_id = sb.staff_id join
           mark ma
           on ma.subject_id = su.subject_id 
     ) s
order by max_mark desc;

你也可以用你的方法( select distinct 具有窗口功能）。但你必须面对 subject_name :

select distinct staff_name,
       first_value(subject_name) over (partition by sb.staff_id order by value desc) as subject_name
       max(value) over (partition by sb.staff_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;