Mysql 单表查询各班级总分前三名

select class,total,name from (select *,ywsc+sxsc as total from st ORDER BY total DESC) b

where

not EXISTS(select * from (select *,ywsc+sxsc as total from st ORDER BY total DESC) c where c.class=b.class and b.total <c.total GROUP BY c.class HAVING COUNT(*)>2 )

ORDER BY b.class,b.total DESC

上面那条答案应该是可以的啊，可能是看起来复杂了点吧，我自己试过是没问题的：

SELECT t1.sno,t1.cno,Score

FROM SC t1

WHERE EXISTS

(SELECT COUNT(1)

FROM SC

WHERE t1.cno= cno AND t1.score<score

HAVING COUNT(1)<3)

ORDER BY t1.cno,score DESC

另外还有一种类似的写法：

SELECT t1.sno,t1.cno,Score

FROM SC t1

WHERE

(SELECT COUNT(cno)

FROM SC

WHERE t1.cno= cno AND t1.score<score)<3

ORDER BY t1.cno,score DESC

---