求每个课程前两名的MySQL做法

表结构如下

/*!40101 SET NAMES utf8 */;

create table `grade` (
    `sid` int ,
    `cid` int ,
    `ac` int 
); 
insert into `grade` (`sid`, `cid`, `ac`) values('1','1','99');
insert into `grade` (`sid`, `cid`, `ac`) values('2','1','98');
insert into `grade` (`sid`, `cid`, `ac`) values('3','2','100');
insert into `grade` (`sid`, `cid`, `ac`) values('4','2','100');
insert into `grade` (`sid`, `cid`, `ac`) values('5','2','98');
insert into `grade` (`sid`, `cid`, `ac`) values('7','1','97');

 

解决办法如下：

SELECT sid ,cid ,ac FROM grade AS a
WHERE (SELECT COUNT(*) FROM grade AS b  WHERE a.cid=b.`cid` AND a.`ac`<=b.`ac` )<=2
ORDER BY a.`cid`,a.`ac` DESC;