三种方式：
第一种是比大小，然后计数
select t.score,(
select count(1) from tmptest where score<=t.score
) from tmptest t
第二种是利用笛卡尔积，两对比排序
-- 学校类型数据
SELECT t.examid,'-' AS unitcode,t.schooltype,'-' AS classname,t.bkrs,t.skrs,t.avgs,
SUM(IF(t.avgs<=lt.avgs,lt.avgrank,0))+1-t.avgrank AS avgrank,
t.anum,t.aratio,
SUM(IF(t.aratio<=lt.aratio,lt.arrank,0))+1-t.arrank AS arrank,
t.bnum,t.bratio,
SUM(IF(t.bratio<=lt.bratio,lt.brrank,0))+1-t.brrank AS brrank,
'' AS ordernum
FROM
(
SELECT
schooltypeinfo.examid,schooltypeinfo.objid,schooltypeinfo.testpaperid,schooltypeinfo.wl,schooltypeinfo.subjectid,schooltypeinfo.scoreType,schooltypeinfo.objType,
schooltypeinfo.schooltype,schooltypeinfo.bkrs,schooltypeinfo.skrs,schooltypeinfo.avgs,
schooltypeaa.anum,ROUND(schooltypeaa.anum/schooltypeinfo.skrs,4) aratio
,schooltypebb.bnum,ROUND(schooltypebb.bnum/schooltypeinfo.skrs,4) bratio,
COUNT(1)AS avgrank,COUNT(1) AS arrank,COUNT(1) AS brrank
FROM
(
SELECT
dast.examid,dast.objid,dast.testpaperid,dast.wl,dast.subjectid,dast.scoreType,dast.objType,
dast.schooltype,dast.bkrs,dast.skrs,ROUND(dast.avgs,2) avgs
FROM `dw_agg_schooltype_totalscore` dast
WHERE dast.examid = 2021 AND dast.subjectid = 999 AND dast.`scoreType` = 1
)schooltypeinfo INNER JOIN (
SELECT dass.schooltype,SUM(IF(dass.score >= @ascore,dass.num,0)) anum FROM dw_agg_schooltype_segment dass WHERE dass.examid = 2021 AND dass.objid = 20002 AND dass.subjectid = 999 GROUP BY dass.schooltype -- 学校类型A优人数
)schooltypeaa ON schooltypeinfo.schooltype = schooltypeaa.schooltype
INNER JOIN(
SELECT dass.schooltype,SUM(IF(dass.score >= @bscore,dass.num,0)) bnum FROM dw_agg_schooltype_segment dass WHERE dass.examid = 2021 AND dass.objid = 20002 AND dass.subjectid = 999 GROUP BY dass.schooltype -- 学校类型B优人数
) schooltypebb ON schooltypeinfo.schooltype = schooltypebb.schooltype
GROUP BY schooltype
) t
LEFT JOIN
(
SELECT
schooltypeinfo.examid,schooltypeinfo.objid,schooltypeinfo.testpaperid,schooltypeinfo.wl,schooltypeinfo.subjectid,schooltypeinfo.scoreType,schooltypeinfo.objType,
schooltypeinfo.schooltype,schooltypeinfo.bkrs,schooltypeinfo.skrs,schooltypeinfo.avgs,
schooltypeaa.anum,ROUND(schooltypeaa.anum/schooltypeinfo.skrs,4) aratio
,schooltypebb.bnum,ROUND(schooltypebb.bnum/schooltypeinfo.skrs,4) bratio,
COUNT(1)AS avgrank,COUNT(1) AS arrank,COUNT(1) AS brrank
FROM
(
SELECT
dast.examid,dast.objid,dast.testpaperid,dast.wl,dast.subjectid,dast.scoreType,dast.objType,
dast.schooltype,dast.bkrs,dast.skrs,ROUND(dast.avgs,2) avgs
FROM `dw_agg_schooltype_totalscore` dast
WHERE dast.examid = 2021 AND dast.subjectid = 999 AND dast.`scoreType` = 1
)schooltypeinfo INNER JOIN (
SELECT dass.schooltype,SUM(IF(dass.score >= @ascore,dass.num,0)) anum FROM dw_agg_schooltype_segment dass WHERE dass.examid = 2021 AND dass.objid = 20002 AND dass.subjectid = 999 GROUP BY dass.schooltype -- 学校类型A优人数
)schooltypeaa ON schooltypeinfo.schooltype = schooltypeaa.schooltype
INNER JOIN(
SELECT dass.schooltype,SUM(IF(dass.score >= @bscore,dass.num,0)) bnum FROM dw_agg_schooltype_segment dass WHERE dass.examid = 2021 AND dass.objid = 20002 AND dass.subjectid = 999 GROUP BY dass.schooltype -- 学校类型B优人数
) schooltypebb ON schooltypeinfo.schooltype = schooltypebb.schooltype
GROUP BY schooltype
) lt
ON t.examid=lt.examid
AND t.objid=lt.objid
AND t.testpaperId=lt.testpaperId
AND t.wl=lt.wl
AND t.subjectid=lt.subjectid
GROUP BY t.examid,t.objid,t.testpaperId,t.subjectId,t.avgs,t.schooltype,t.scoreType,t.wl,t.objType
第三种是利用变量对比加1
select a.*,
@rownum:=@rownum+1 AS rownum,
IF(@total=a.score,@rank,@rank:=@rownum) AS rank,
@total:=a.score tscore
from sqooptest a,(SELECT @rank:=1, @rownum:=0) b order by a.score desc