mysql 单表排序，相同值排序

 三种方式：

第一种是比大小，然后计数

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,
'2' 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