mysql 按相似度排序_mysql 单表排序，相同值排序

最新推荐文章于 2024-11-14 11:03:45 发布

weixin_39942785

最新推荐文章于 2024-11-14 11:03:45 发布

阅读量174

点赞数

文章标签： mysql 按相似度排序

本文链接：https://blog.csdn.net/weixin_39942785/article/details/113114044

版权

这篇博客介绍了如何使用 MySQL 查询语句，根据相似度对学校类型数据进行排序。通过复杂的 SQL 查询，结合了多个表格的数据，计算出平均排名、优等生比例等指标，并对这些指标进行排序，从而实现数据的精细化分析。

摘要由CSDN通过智能技术生成

--学校类型数据

SELECT t.examid,'-' AS unitcode,t.schooltype,'-' ASclassname,t.bkrs,t.skrs,t.avgs,SUM(IF(t.avgs<=lt.avgs,lt.avgrank,0))+1-t.avgrank ASavgrank,

t.anum,t.aratio,SUM(IF(t.aratio<=lt.aratio,lt.arrank,0))+1-t.arrank ASarrank,

t.bnum,t.bratio,SUM(IF(t.bratio<=lt.bratio,lt.brrank,0))+1-t.brrank ASbrrank,'2' ASordernumFROM(SELECTschooltypeinfo.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) ASbrrankFROM(SELECTdast.examid,dast.objid,dast.testpaperid,dast.wl,dast.subjectid,dast.scoreType,dast.objType,

dast.schooltype,dast.bkrs,dast.skrs,ROUND(dast.avgs,2) avgsFROM`dw_agg_schooltype_totalscore` dastWHERE dast.examid = 2021 AND dast.subjectid = 999 AND dast.`scoreType` = 1)schooltypeinfoINNER 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.schooltypeINNER 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.schooltypeGROUP BYschooltype

) tLEFT JOIN(SELECTschooltypeinfo.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 ON schooltypeinfo.schooltype =schooltypebb.schooltypeGROUP BYschooltype

) ltON t.examid=lt.examidAND t.objid=lt.objidAND t.testpaperId=lt.testpaperIdAND t.wl=lt.wlAND t.subjectid=lt.subjectidGROUP BY t.examid,t.objid,t.testpaperId,t.subjectId,t.avgs,t.schooltype,t.scoreType,t.wl,t.objType