成绩管理系统中的成绩报表SQL

成绩模块报表SQL

一：对指定考试、科目、分制、班级下的学生成绩进行排名
public static DataTable GetScoreCount(string testinfoid, string classid,string scoretype, string subjname, string uapid, string unitId);

SQL语句说明：
单科目排名，分制不一样，成绩的等级不一样，但SQL的结构一样，以100分制为例：
TABLE:DETAIL_SCORE
提示：SCORE_STAT为统计成绩字段
实现过程：从DETAIL_SCORE表中根据SCORE_STAT计算出"排名",再从包含"排名"的子查询中获取需要的字段，
成绩"等级"根据SCORE_STAT的值显示成不同的标示。
SQL层次化后结构如下：

"SELECT STUDENT_NAME AS 姓名,
COURSE_NAME AS 科目,
SCORE_STAT AS 成绩,
case when SCORE_STAT>=90 then '优秀'
when SCORE_STAT>=80 and SCORE_STAT<90 then '良好'
when SCORE_STAT>=70 and SCORE_STAT<80 then '中等'
when SCORE_STAT>=60 and SCORE_STAT<70 then '及格'
when SCORE_STAT <60 then '不及格' else '不及格'
end AS 等级,
排名
FROM(
SELECT STUDENT_NAME,
SCORE_STAT ,
COURSE_NAME,
(
SELECT count(distinct SCORE_STAT ) FROM DETAIL_SCORE
WHERE SCORE_STAT >a.SCORE_STAT and TEST_ID='" + testinfoid + "' and CLASS_ID=" + classid + " and unit_id=" + unitId + " and SCORE_TYPE='" + scoretype + "' and COURSE_NAME='" + subjname + "'
)+1 AS 排名
FROM DETAIL_SCORE a WHERE TEST_ID='" + testinfoid + "' and CLASS_ID=" + classid + " and SCORE_TYPE='" + scoretype + "' and COURSE_NAME='" + subjname + "'
ORDER BY 排名

) x ORDER BY 排名 ASC;"

二.查询总分排名，所有科目的总分
public static DataTable GetSumTjScore(string testinfoid, string classid,string scoretypeid, string uapid, string unitId);

SQL语句说明：
总分排名，分制不一样，成绩的等级不一样，但SQL的结构一样，以100分制为例：
最终结果：显示一个班级的每个学生的各个科目的成绩，等级和班级排名，各个科目的总分以及总分的排名。
TABLE:DETAIL_SCORE
提示：SCORE_STAT为统计成绩字段
实现过程：从DETAIL_SCORE表中按照学生名分组，对SCORE_STAT进行求和计算总成绩,然后根据总成绩计算出总成绩排名。
SQL层次化后结构如下：
SELECT
STUDENT_NAME AS 姓名,
sum_score,
(
SELECT count(distinct sum_score)
FROM
(
SELECT STUDENT_NAME,
sum(SCORE_STAT) AS sum_score
FROM DETAIL_SCORE
WHERE TEST_ID='83M8KLQI00IV9LQE' and CLASS_ID=1 and unit_id=41001 and SCORE_TYPE='百分制'
GROUP BY STUDENT_NAME
) a
WHERE a.sum_score>b.sum_score
)+1 AS sum_place
FROM(
SELECT STUDENT_NAME,sum(SCORE_STAT) AS sum_score
FROM DETAIL_SCORE
WHERE TEST_ID='83M8KLQI00IV9LQE' and CLASS_ID=1 and unit_id=41001 and SCORE_TYPE='百分制'
GROUP BY STUDENT_NAME
) AS b
ORDER BY sum_place;

三.查询平均分排名,某一科目的平均分
public static DataTable GetSubjAvgScore(string scoretype, string testinfoid,string testsubjname, string inclass, string uapid, string unitId);

SQL语句说明：
平均成绩排名，分制不一样，成绩的等级不一样，但SQL的结构一样，以100分制为例：
最终结果：显示每个班级的某科目的平均成绩，各个成绩等级的人数，以及班级平均成绩在年级中的排名。
TABLE:DETAIL_SCORE
提示：SCORE_STAT为统计成绩字段
实现过程：从DETAIL_SCORE表中计算某科目下每个班级的平均成绩，各个等级的人数，班级排名；然后根据平均饭计算班级的排名；

SQL层次化后结构如下：

SELECT CLASS_ID as 班号,CLASS_NAME as 班级,COURSE_NAME as 科目,
avg_score as 平均分,优秀,良好,中等,及格,不及格,
(
SELECT count(distinct avg_score)
FROM(
SELECT CLASS_ID,
CLASS_NAME,
COURSE_NAME,
round(avg(SCORE_STAT),2) as avg_score
FROM DETAIL_SCORE
WHERE CLASS_ID in (1,2,3) and unit_id=41001 and TEST_ID='83M8KLQI00IV9LQE' and COURSE_NAME='语文'
GROUP BY CLASS_ID
) AS aa
WHERE aa.avg_score>bb.avg_score
)+1 as 班级排名
FROM (
SELECT CLASS_ID,CLASS_NAME,
COURSE_NAME,
(
SELECT count(SCORE_STAT)
FROM DETAIL_SCORE b
WHERE SCORE_STAT>=90 and b.CLASS_ID=a.CLASS_ID and b.unit_id=a.unit_id and TEST_ID='83M8KLQI00IV9LQE' and COURSE_NAME='语文'
) as 优秀,
(
SELECT count(SCORE_STAT)
FROM DETAIL_SCORE c
WHERE SCORE_STAT>=80 and SCORE_STAT<90 and c.CLASS_ID=a.CLASS_ID and c.unit_id=a.unit_id and TEST_ID='83M8KLQI00IV9LQE' and COURSE_NAME='语文'
) as 良好,
(
SELECT count(SCORE_STAT)
FROM DETAIL_SCORE d
WHERE SCORE_STAT>=70 and SCORE_STAT<80 and d.CLASS_ID=a.CLASS_ID and d.unit_id=a.unit_id and TEST_ID='83M8KLQI00IV9LQE' and COURSE_NAME='语文'
) as 中等,
(
SELECT count(SCORE_STAT)
FROM DETAIL_SCORE e
WHERE SCORE_STAT>=60 and SCORE_STAT<70 and e.CLASS_ID=a.CLASS_ID and e.unit_id=a.unit_id and TEST_ID='83M8KLQI00IV9LQE' and COURSE_NAME='语文'
) as 及格,
(
SELECT count(SCORE_STAT)
FROM DETAIL_SCORE f
WHERE SCORE_STAT<60 and f.CLASS_ID=a.CLASS_ID and f.unit_id=a.unit_id and TEST_ID='83M8KLQI00IV9LQE' and COURSE_NAME='语文'
) as 不及格,
round(avg(SCORE_STAT),2) as avg_score
FROM DETAIL_SCORE a
WHERE CLASS_ID in (1,2,3) and unit_id=41001 and TEST_ID='83M8KLQI00IV9LQE' and COURSE_NAME='语文'
GROUP BY CLASS_ID
) bb
ORDER BY 班级排名;