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