sql - Oracle / 非技术区

score表
scoreid course studentid score
1 数学 2 99
2 数学 3 60
3 数学 4 80
4 语文 5 79
5 语文 6 58
6 语文 1 66
7 英语 6 76
8 英语 4 87
9 英语 3 100
10英语 2 69

怎样得到没门课中分数最高的那条记录

---

select * from score a where not exists( select * from score b where a.score<b.score);

---

select * from score a where not exists( select * from score b where a.score <b.score) group by course;

---

select * from score a where not exists( select * from score b where a.score <b.score and a.course = b.course);

---

SQL code:
select t.* from score t where score = (select max(score) from score where course = t.course)

select t.* from score t where not exists (select 1 from score where course = t.course and score > t.score)

---

select studentid,course,score from (
select studentid,course,score,row_number() over(partition by course order by score desc) rn from score
) where rn = 1

---

学习了。

---

SQL code:
WITH tmp_table AS
(
SELECT T.SCOREID,
T.COURSE,
T.SCORE,
T.STUDENTID,
ROW_NUMBER() OVER(PARTITION BY T.COURSE ORDER BY T.SCORE DESC) AS RN
from SCORE T
)
SELECT
ta.SCOREID,
ta.COURSE,
ta.ST