¹òÇóÒ»¸ösqlÓï¾ä¡£

³É¼¨±íStuScore£º¿Î³ÌÃûcourseName¡¢Ñ§ºÅstudNo¡¢ÐÕÃûname¡¢³É¼¨score
ÇóȫУÿÃſγ̿¼ÊԳɼ¨ÅÅÃûÇ°ÈýÃûµÄѧÉúÃûµ¥
Êä³ö ¿Î³ÌÃû ÐÕÃû ³É¼¨


ÏëÁËN¾ÃÏë²»³öÀ´£¬Çó´óϺÃÇ°ï°ïæ

---

SQL code:
select courseName,name,score
from StuScore a
where 3>=(select count(*) from tb where courseName=a.courseName and score>=a.score)

---

ÅÅÃû±ØÈ»Éæ¼°µ½Í¬·ÖÈçºÎÅŵÄÎÊÌâ

---

SQL code:
select name from
(
select *,row_number() over(partition by courseName order by score desc) rank
from StuScore
)tt
where rank<=3

---

SQL code:
-sql2000


select name from
(
select *,
(select count(*) from StuScore where courseName=s.courseName and score<s.score) rank
from StuScore
) tt
where rank<=3

-sql2005
select name from
(
select *,row_number() over(partition by courseName order by score desc) rank
from StuScore
)tt
where rank<=3

---

select *
from StuScore ms
inner join
(
select courseName+score cs
from StuScore ss
where score > (select top 3 score from StuScore s1 where s1.CourseName = ss.CourseName)
) tmp
wh