本題技巧說明 1.join 2.group by 3.rank

SQL語法：

RANK() OVER (

    PARTITION BY [{,...}]

    ORDER BY [ASC|DESC], [{,...}]

)

在這個語法中：

PARTITION BY子句對結果集進行分區。

RANK()功能在分區內執行

ORDER BY子句按一個或多個列或運算式對分區內的行進行排序。

注意，over()裡頭的分組以及排序的執行晚於 where 、group by、 order by 的執行。

============================

 

自體連結找出成績組合

select *

from sc as s1

inner join sc s2

on s1.cno = s2.cno and s1.score > s2.score;

科目相同列出成績較高組合

 

 

 

 

去除重複並依科目排列

select s1.sno ,s1.cno ,s1.score

from sc as s1

inner join sc s2

on s1.cno = s2.cno and s1.score > s2.score

GROUP by s1.cno ,s1.score ;

設定排名

select s1.sno ,s1.cno ,s1.score ,
rank() over(partition by s1.cno ORDER  by s1.cno,s1.score desc)  as nums

from sc as s1

inner join sc s2

on s1.cno = s2.cno and s1.score > s2.score

GROUP by s1.cno ,s1.score

 

排名<=3

select * from

(

  select s1.sno ,s1.cno ,s1.score ,
rank() over(partition by s1.cno ORDER  by s1.cno,s1.score desc)  as nums

  from sc as s1

  inner join sc s2

  on s1.cno = s2.cno and s1.score > s2.score

  GROUP by s1.cno ,s1.score

)

where nums <=3

修改自https://www.jianshu.com/p/fff5d1f71c0f