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 *

from(

SELECT

obj.id,

obj.score,

CASE

WHEN @rowtotal = obj.score THEN

@rownum

WHEN @rowtotal := obj.score THEN

@rownum :=@rownum + 1

WHEN @rowtotal = 0 THEN

@rownum :=@rownum + 1

END AS rownum

FROM

(

SELECT id,name,score

fromtest -- 表的名字

ORDER BY score DESC

）) AS obj,

(SELECT @rownum := 0 ,@rowtotal := NULL) r

) as no2

where rownum <4

---