表(test)
id name cid score
1 a 1 95
2 b 1 98
3 c 1 99
4 d 1 96
5 e 2 99
6 f 2 97
7 g 2 99
8 h 2 93
#取出每组中分数的最后两名
select a.* from test as a where(
select count(*) from test as b where a.cid = b.cid and a.score > b.score
) <=1 order by sid,score
#比本身分数小的个数小于等于1 (最小的是0 ,次之是1)
#同理每组中的前两名
select a.* from test as a where(
select count(*) from test as b where a.cid = b.cid and a.score < b.score
) <=1 order by sid,score desc
#比本身分数大的个数小于等于1 (最大的是0 ,次之是1)