成绩统计、sql记录

最新推荐文章于 2024-10-18 20:50:24 发布

david_yao

最新推荐文章于 2024-10-18 20:50:24 发布

阅读量488

点赞数

本文链接：https://blog.csdn.net/david_yao/article/details/4557634

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

有表如下：

试卷表M_EXAMINATION_PAPER(ID，EXAMINATION_TYPE_ID)

学生的试卷得分dbo.M_EXAMINATION_SCORE(ID,EXAMINATION_PAPER_ID，USERNAME，SCORE)

试卷的班级平均分dbo.M_EXAMINATION_CLASS_AVG(PAPER_ID，SCHOOL_ID，GRADE_CODE，CLASS_NO，AVG_CLASS)

试卷的年级平均分dbo.M_EXAMINATION_GRADE_AVG(PAPER_ID，SCHOOL_ID，GRADE_CODE，AVG_GRADE)

查询出XX学年XX学期期中(期末)某生的各科原始分、班级平均分、班级排名、年级平均分、年级排名

SELECT S.EXAMINATION_PAPER_ID,S.USERNAME,S.SCORE,SUBJECT.NAME,P.ID ,AVG_C.SCHOOL_ID ,AVG_C.GRADE_CODE ,AVG_C.CLASS_NO ,SC.AVG_CLASS ,GR.AVG_GRADE ,AVG_C.RANK AS 班级排名 ,AVG_G.RANK AS 年级排名 FROM M_EXAMINATION_SCORE S INNER JOIN dbo.M_EXAMINATION_CLASS_AVG SC ON SC.PAPER_ID = S.EXAMINATION_PAPER_ID INNER JOIN dbo.M_EXAMINATION_GRADE_AVG GR ON GR.PAPER_ID = S.EXAMINATION_PAPER_ID INNER JOIN M_EXAMINATION_PAPER P ON S.EXAMINATION_PAPER_ID = P.ID INNER JOIN M_SUBJECT SUBJECT ON SUBJECT.ID = P.SUBJECT_ID INNER JOIN (SELECT SC.EXAMINATION_PAPER_ID AS PAPER_ID,SC.USERNAME,SC.SCORE,S.SCHOOL_ID,S.GRADE_CODE,S.CLASS_NO,RANK() OVER (PARTITION BY EXAMINATION_PAPER_ID, S.SCHOOL_ID,S.GRADE_CODE,S.CLASS_NO order by SC.SCORE desc) as RANK FROM M_EXAMINATION_SCORE SC INNER JOIN M_STUDENT_INFO S ON SC.USERNAME = S.CARD_ID ) AVG_C ON AVG_C.PAPER_ID = S.EXAMINATION_PAPER_ID INNER JOIN (SELECT SC.EXAMINATION_PAPER_ID AS PAPER_ID,SC.USERNAME,SC.SCORE,S.SCHOOL_ID,S.GRADE_CODE,S.CLASS_NO,RANK() OVER (PARTITION BY EXAMINATION_PAPER_ID, S.SCHOOL_ID,S.GRADE_CODE order by SC.SCORE desc) as RANK FROM M_EXAMINATION_SCORE SC INNER JOIN M_STUDENT_INFO S ON SC.USERNAME = S.CARD_ID ) AVG_G ON AVG_G.PAPER_ID = S.EXAMINATION_PAPER_ID WHERE S.USERNAME='stu1570' AND S.EXAMINATION_PAPER_ID IN( SELECT ID FROM M_EXAMINATION_PAPER WHERE SCHOOL_ID = '15' AND BOUND_ID='010' AND EXAMINATION_TYPE_ID=1 AND EXAM_TIME BETWEEN '2006-10-1' AND '2006-12-30' ) AND SC.SCHOOL_ID = ( SELECT SCHOOL_ID FROM M_STUDENT_INFO WHERE CARD_ID = 'stu1570' ) AND SC.GRADE_CODE = ( SELECT GRADE_CODE FROM M_STUDENT_INFO WHERE CARD_ID = 'stu1570' ) AND SC.CLASS_NO = ( SELECT CLASS_NO FROM M_STUDENT_INFO WHERE CARD_ID = 'stu1570' ) AND GR.SCHOOL_ID = ( SELECT SCHOOL_ID FROM M_STUDENT_INFO WHERE CARD_ID = 'stu1570' ) AND GR.GRADE_CODE = ( SELECT GRADE_CODE FROM M_STUDENT_INFO WHERE CARD_ID = 'stu1570' ) AND AVG_C.SCHOOL_ID = ( SELECT SCHOOL_ID FROM M_STUDENT_INFO WHERE CARD_ID = 'stu1570' ) AND AVG_C.GRADE_CODE = ( SELECT GRADE_CODE FROM M_STUDENT_INFO WHERE CARD_ID = 'stu1570' ) AND AVG_C.CLASS_NO = ( SELECT CLASS_NO FROM M_STUDENT_INFO WHERE CARD_ID = 'stu1570' ) AND AVG_C.USERNAME='stu1570' AND AVG_C.PAPER_ID IN ( SELECT ID FROM M_EXAMINATION_PAPER WHERE SCHOOL_ID = '15' AND BOUND_ID='010' AND EXAMINATION_TYPE_ID=1 AND EXAM_TIME BETWEEN '2006-10-1' AND '2006-12-30' ) AND AVG_G.SCHOOL_ID = ( SELECT SCHOOL_ID FROM M_STUDENT_INFO WHERE CARD_ID = 'stu1570' ) AND AVG_G.GRADE_CODE = ( SELECT GRADE_CODE FROM M_STUDENT_INFO WHERE CARD_ID = 'stu1570' ) AND AVG_G.USERNAME='stu1570' AND AVG_G.PAPER_ID IN ( SELECT ID FROM M_EXAMINATION_PAPER WHERE SCHOOL_ID = '15' AND BOUND_ID='010' AND EXAMINATION_TYPE_ID=1 AND EXAM_TIME BETWEEN '2006-10-1' AND '2006-12-30' )

运行结果

PS：所有考试各科各班排名情况

所有考试各科各年级排名情况

select SC.EXAMINATION_PAPER_ID,SC.USERNAME,SC.SCORE,S.SCHOOL_ID,S.GRADE_CODE,RANK() OVER (PARTITION BY EXAMINATION_PAPER_ID,S.SCHOOL_ID,S.GRADE_CODE,S.CLASS_NO order by SC.SCORE desc) as 排名 from M_EXAMINATION_SCORE SC INNER JOIN M_STUDENT_INFO S ON SC.USERNAME = S.CARD_ID

语句①过于复杂，效率低下，新建一个每张试卷每个学生的年纪排名和班级排名

select SC.EXAMINATION_PAPER_ID,SC.USERNAME,SC.SCORE,S.SCHOOL_ID,S.GRADE_CODE,S.CLASS_NO, RANK() OVER (PARTITION BY EXAMINATION_PAPER_ID,S.SCHOOL_ID,S.GRADE_CODE ORDER BY SC.SCORE DESC) AS RAND_GRADE, RANK() OVER (PARTITION BY EXAMINATION_PAPER_ID,S.SCHOOL_ID,S.GRADE_CODE,S.CLASS_NO ORDER BY SC.SCORE DESC) AS RAND_CLASS INTO M_EXAMINATION_RANK from M_EXAMINATION_SCORE SC INNER JOIN M_STUDENT_INFO S ON SC.USERNAME = S.CARD_ID

改写语句①

SELECT S.EXAMINATION_PAPER_ID,S.USERNAME,S.SCORE,SUBJECT.NAME,P.ID ,RANK.SCHOOL_ID ,RANK.GRADE_CODE ,RANK.CLASS_NO ,SC.AVG_CLASS ,GR.AVG_GRADE ,RANK.RANK_CLASS AS 班级排名 ,RANK.RANK_GRADE AS 年级排名 FROM M_EXAMINATION_SCORE S INNER JOIN M_EXAMINATION_CLASS_AVG SC ON SC.PAPER_ID = S.EXAMINATION_PAPER_ID INNER JOIN M_EXAMINATION_GRADE_AVG GR ON GR.PAPER_ID = S.EXAMINATION_PAPER_ID INNER JOIN M_EXAMINATION_PAPER P ON S.EXAMINATION_PAPER_ID = P.ID INNER JOIN M_SUBJECT SUBJECT ON SUBJECT.ID = P.SUBJECT_ID INNER JOIN M_EXAMINATION_RANK RANK ON RANK.EXAMINATION_PAPER_ID = S.EXAMINATION_PAPER_ID AND RANK.USERNAME=S.USERNAME INNER JOIN M_STUDENT_INFO STU ON STU.CARD_ID = S.USERNAME AND STU.CARD_ID=RANK.USERNAME AND SC.SCHOOL_ID = STU.SCHOOL_ID AND SC.GRADE_CODE = STU.GRADE_CODE AND SC.CLASS_NO = STU.CLASS_NO AND GR.SCHOOL_ID = STU.SCHOOL_ID AND GR.GRADE_CODE = STU.GRADE_CODE WHERE S.USERNAME='stu1570' AND S.EXAMINATION_PAPER_ID IN( SELECT ID FROM M_EXAMINATION_PAPER WHERE SCHOOL_ID = '15' AND BOUND_ID='010' AND EXAMINATION_TYPE_ID=1 AND EXAM_TIME BETWEEN '2006-10-1' AND '2006-12-30' )