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

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