sql

[chwwwdz]

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

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

[crazylaa]

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

[crazylaa]

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

[crazylaa]

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

[dawugui]

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)

[Phoenix_99]

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

[sharpidd]

学习了。

[a9529lty]

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.STUDENTID,
ta.SCORE
FROM
tmp_table ta 
WHERE
ta.rn = 1

[忆往惜_追风]

SQL語句又學習了一遍

學習了