【SQL 學習】一個面試題

今天面試的時候，問了一個sql編寫的題目， 求每門成績的最高分數的id 以及科目，分數。（當時沒有寫好，鬱悶了，面試官還是很好的，給我講解了一下！）回到宿舍自己有寫了一下，兩個方法：

SQL> create table test (id int ,subject varchar2(20), score int);
表已建立。
SQL> insert into test values(1 ,'math',95);
已建立 1 行。
SQL> insert into test values(2 ,'math',92);
已建立 1 行。
SQL> insert into test values(3 ,'math',97);
已建立 1 行。
SQL> insert into test values(3 ,'english',97);
已建立 1 行。
SQL> insert into test values(3 ,'chiness',79);
已建立 1 行。
SQL> insert into test values(1,'chiness',79);
已建立 1 行。
SQL> insert into test values(2,'chiness',89);
已建立 1 行。
SQL> insert into test values(1,'english',96);
已建立 1 行。
SQL> insert into test values(2,'english',86);
已建立 1 行。
SQL> commit;
提交完成。
SQL> select * from test;

        ID SUBJECT                   SCORE                                     
---------- -------------------- ----------                                     
         1 math                         95                                     
         2 math                         92                                     
         3 math                         97                                     
         3 english                      97                                     
         3 chiness                      79                                     
         1 chiness                      79                                     
         2 chiness                      89                                     
         1 english                      96                                     
         2 english                      86                                     

已選擇9行。

SQL> select id ,subject,score from
  2  (select id ,subject,score,row_number() over (partition by subject
  3  order by score desc ) rn from test )
  4  where rn=1;

        ID SUBJECT                   SCORE                                     
---------- -------------------- ----------                                     
         2 chiness                      89                                     
         3 english                      97                                     
         3 math                         97                                     

SQL> select id ,subject,score from
  2  test t3 ,(select max(score) sco from test group by subject) t4
  3  where t4.sco=t3.score;

        ID SUBJECT                   SCORE                                     
---------- -------------------- ----------                                     
         3 english                      97                                     
         3 math                         97                                     
         3 english                      97                                     
         3 math                         97                                     
         2 chiness                      89                                     

SQL> select distinct id ,subject,score from
  2  test t3 ,(select max(score) sco from test group by subject) t4
  3  where t4.sco=t3.score ;

        ID SUBJECT                   SCORE                                     
---------- -------------------- ----------                                     
         3 english                      97                                     
         3 math                         97                                     
         2 chiness                      89