MySQL统计期末学生联考成绩

之前一直使用Excel来做成绩分析,统计各班级,科目对号率非常麻烦,还是MySQL方便一些.

一、表结构

一共3个表:

学生总分成绩表finalscore
- opt:组合方向,物理或历史
- optional:选科,包含首选科目和再选科目
- score:总分

CREATE TABLE `finalscore` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `num` CHAR(8) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
    `class` INT(1) NULL DEFAULT NULL,
    `name` CHAR(10) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
    `optional` CHAR(10) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
    `opt` CHAR(10) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
    `score` FLOAT NULL DEFAULT NULL,
    `origin_score` FLOAT NULL DEFAULT NULL,
    `grade` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
    `s_order` INT(11) NULL DEFAULT NULL,
    `f_order` INT(11) NULL DEFAULT NULL,
    `o_order` INT(11) NULL DEFAULT NULL,
    `c_order` INT(11) NULL DEFAULT NULL,
    `cf_order` INT(11) NULL DEFAULT NULL,
    `co_order` INT(11) NULL DEFAULT NULL,
    `comment` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
    PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=512
;

学生单科成绩表score
- type:科目,如:语文,历史或者政治
- score:单科赋分分数
- origin_score:单科原始分数
- a_score:总分

CREATE TABLE `score` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `num` CHAR(8) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
    `class` INT(1) NULL DEFAULT NULL,
    `name` CHAR(10) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
    `optional` CHAR(10) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
    `opt` CHAR(10) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
    `score` FLOAT NULL DEFAULT NULL,
    `origin_score` FLOAT NULL DEFAULT NULL,
    `a_score` FLOAT NULL DEFAULT NULL,
    `a_origin_score` FLOAT NULL DEFAULT NULL,
    `grade` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
    `type` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
    `s_order` INT(11) NULL DEFAULT NULL,
    `f_order` INT(11) NULL DEFAULT NULL,
    `o_order` INT(11) NULL DEFAULT NULL,
    `c_order` INT(11) NULL DEFAULT NULL,
    `cf_order` INT(11) NULL DEFAULT NULL,
    `co_order` INT(11) NULL DEFAULT NULL,
    `comment` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
    PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
ROW_FORMAT=DYNAMIC
AUTO_INCREMENT=4096
;

划线标准表stld

CREATE TABLE `stld` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `t_score` FLOAT NULL DEFAULT NULL,
  `b_score` FLOAT NULL DEFAULT NULL,
  `opt` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
  `type` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
  PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=32
;

二、部分计算方法

统计各班分数数据

统计各班的考试人数,最高分,平均分,最低分根据班级统计:GROUP BY class

SELECT class,COUNT(NAME),MAX(score),round(AVG(score),1),MIN(score) FROM finalscore GROUP BY class

统计各班,各科目数据
统计各班各科目考试人数,最高分,平均分,最低分
- 根据班级,学科统计:GROUP BY score.class,score.type

SELECT class AS 班级,TYPE AS 学科,count(NAME) as 人数,max(score) AS 最高分,round(AVG(score),1) AS 平均分,MIN(score) AS 最低分 FROM score GROUP BY class,TYPE ORDER BY type,class;

统计各班本科上线数据
- 本科上线条件:JOIN stld ON score>=b_score AND finalscore.opt=stld.opt AND stld.type='总分'
- 根据班级和方向统计:GROUP BY class,finalscore.opt

SELECT class,COUNT(num) FROM finalscore JOIN stld ON score>=b_score AND finalscore.opt=stld.opt AND stld.type='总分' GROUP BY class,finalscore.opt

统计各班单科上线数据
统计各班,单科上线名单
- 选科方向,选科,单科成绩上线JOIN stld ON (score.`type`=stld.`type` AND score.opt=stld.opt AND score.score>=stld.b_score)
- 根据班级,学科统计:GROUP BY score.class,score.type

SELECT score.class AS 班级,score.`type` AS 学科,COUNT(num) AS 人数 from score JOIN stld ON (score.`type`=stld.`type` AND score.opt=stld.opt AND score.score>=stld.t_score) GROUP BY score.class,score.type ORDER BY score.type,score.class

统计各班,单科命中数据
命中:总分上线且本学科上线统计各班总分上线,同时单科成绩也上线人数
- 选科方向,选科,单科成绩上线JOIN stld ON (score.`type`=stld.`type` AND score.opt=stld.opt AND score.score>=stld.b_score)
- 总分上线:WHERE a_score>=406 AND score.opt='历史'
- 根据班级,学科统计:GROUP BY score.class,score.type

SELECT score.class AS 班级,score.`type` AS 学科,COUNT(num) AS 人数 from score JOIN stld ON (score.`type`=stld.`type` AND score.opt=stld.opt AND score.score>=stld.b_score) WHERE a_score>=406 AND score.opt='历史' GROUP BY score.class,score.type ORDER BY score.type,score.class

查看各班总分且单科上线学生
根据划线标准,查看总分上线,并且单科成绩也上线的学生
- 总分上线:WHERE a_score>=431.5 AND score.opt='物理'
- 选科方向,选科,单科成绩上线JOIN stld ON (score.`type`=stld.`type` AND score.opt=stld.opt AND score.score>=stld.b_score)

SELECT score.num,score.class,score.NAME,score.optional,score.opt,score.score,score.origin_score,score.type, score.a_score,score.a_origin_score from score JOIN stld ON (score.`type`=stld.`type` AND score.opt=stld.opt AND score.score>=stld.b_score) WHERE a_score>=431.5 AND score.opt='物理' ORDER BY name

统计各班日语、英语人数

外语包含英语，日语两个语种，分别统计各班两个语种的考试人数根据班级,语种统计GROUP BY class,comment

SELECT class,TYPE,COMMENT,COUNT(num) FROM score WHERE TYPE='外语' GROUP BY class,comment

统计各班日语,英语上线数据
- 选科方向,选科,单科成绩上线JOIN stld ON (score.`type`=stld.`type` AND score.opt=stld.opt AND score.score>=stld.b_score)
- 根据班级,专业,语种:``GROUP BY score.class,score.type,score.comment````

SELECT score.class AS 班级,score.`type` AS 学科,score.comment,COUNT(num) AS 人数 from score JOIN stld ON (score.`type`=stld.`type` AND score.opt=stld.opt AND score.score>=stld.b_score) WHERE score.type='外语' GROUP BY score.class,score.type,score.`comment` ORDER BY score.class,score.comment

MySQL统计学生期末联考成绩

MySQL统计期末学生联考成绩

一、表结构

二、部分计算方法

results matching ""

No results matching ""

results matching ""

No results matching ""