分组查询取前2条记录的方法（总结）

第一种： 相关子查询，配合top 2  ,order by socre desc

SELECT
a.CId,
a.SId

from SC  a
where  score in (select  top 2 score  from  sc where a.CId= cid  order by score  desc)
order by  a.cid

第二种： 相关子查询，配合score>a.score ,count(*)  直接做为条件

SELECT
a.CId,
a.SId,
a.score
from SC  a
where  (select count(*)  from  sc where a.cid = cid and score > a.score  ) < 2  ORDER BY a.CId

第三种：同表左连接查询，分组group by 后，分组取前2条记录

SELECT a.cid ,a.sid ,a.score FROM sc a LEFT JOIN sc b ON a.cid=b.cid AND a.Score<b.Score group by a.cid ,a.sid ,a.score having count(a.cid)<2 ORDER BY a.cid,a.Score desc;