sql 计算各科成绩排名

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/qq_38966984/article/details/88422675

查询课程01,02,03的成绩排名,先显示01课程再02课程,再03课程

---成绩相同时,并列,比如:并列第一看图片前两项数据
select a.`SId` as '学号' ,
       a.`CId` as '课程id',
       a.score as '成绩',
       (select count(distinct b.score) from `SC` b where a.score<b.score and b.`CId`=a.`CId` )+1 as rank 
   from `SC` a  
   order by a.`CId`, rank

2.成绩相同时,学号大的在后面

select a.`SId` as '学号' ,
       a.`CId` as '课程id',
       a.score as '成绩',
       (select count(b.score)  from `SC` b  where  (a.score<b.score or (a.score=b.score and a.`SId`>b.`SId`) )  and  b.`CId`=a.`CId` and a.`SId`!=b.`SId` )+1 as rank    
from `SC` a 
order by a.`CId`, rank 
展开阅读全文

成绩排名SQL循环问题

12-17

查询成绩的问题已经解决,现在:rnrn一、对查询结果进行排名,我写的有错误rnrn二、有些课程某个班级没有考,我不知道怎么去掉,想着循环应该可以,但是不知道如何入手rnrn[code=SQL]rn--查询成绩rnselect name as 姓名,rnmax(case scoursename when '语文' then descore else 0 end) 语文,rnmax(case scoursename when '政治' then descore else 0 end) 政治,rnmax(case scoursename when '数学' then descore else 0 end) 数学,rnmax(case scoursename when '几何' then descore else 0 end) 几何,rnmax(case scoursename when '英语' then descore else 0 end) 英语,rnmax(case scoursename when '物理' then descore else 0 end) 物理,rnmax(case scoursename when '化学' then descore else 0 end) 化学,rnmax(case scoursename when '生物' then descore else 0 end) 生物,rnmax(case scoursename when '历史' then descore else 0 end) 历史,rnmax(case scoursename when '地理' then descore else 0 end) 地理,rnsum(descore) 总分rnfromrn(select b.name ,a.descore,c.scoursenamernfrom t_card_score a,t_stu b,t_card_course c,t_card_examcou drnwhererna.nstuid=b.id andrna.ncouexamid in (select id from t_card_examcou where nclassid=36 and nexamid=19) andrna.ncouexamid=d.id and rnd.ncourseid=c.idrngroup by b.name,a.descore,c.scoursename)as arngroup by namernorder by 总分 desc[/code]rn效果图:rn[img=http://images.cnblogs.com/cnblogs_com/wengyuli/138664/r_8.bmp][/img]rnrn[code=SQL]rn--排名 我写的有错误 提示:在关键字 'order' 附近有语法错误。rnselect *,rnplace=(select count(descore) from b where descore>b.descore)+1rn(select name as 姓名,rnmax(case scoursename when '语文' then descore else 0 end) 语文,rnmax(case scoursename when '政治' then descore else 0 end) 政治,rnmax(case scoursename when '数学' then descore else 0 end) 数学,rnmax(case scoursename when '几何' then descore else 0 end) 几何,rnmax(case scoursename when '英语' then descore else 0 end) 英语,rnmax(case scoursename when '物理' then descore else 0 end) 物理,rnmax(case scoursename when '化学' then descore else 0 end) 化学,rnmax(case scoursename when '生物' then descore else 0 end) 生物,rnmax(case scoursename when '历史' then descore else 0 end) 历史,rnmax(case scoursename when '地理' then descore else 0 end) 地理,rnsum(descore) 总分rnfromrn(select b.name ,a.descore,c.scoursenamernfrom t_card_score a,t_stu b,t_card_course c,t_card_examcou drnwhererna.nstuid=b.id andrna.ncouexamid in (select id from t_card_examcou where nclassid=36 and nexamid=19) andrna.ncouexamid=d.id and rnd.ncourseid=c.idrngroup by b.name,a.descore,c.scoursename)as arngroup by namernorder by 总分 desc) as brnorder by place[/code]rnrn 论坛

没有更多推荐了,返回首页