MySQL分析学生中考和期末联考成绩

一、表结构

一共3个表

学生信息表info

optional:选科 opt：方向（物理或历史）

CREATE TABLE `info` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `num` CHAR(8) NOT NULL DEFAULT '0' COLLATE 'utf8mb4_general_ci',
    `class` INT(11) NOT NULL DEFAULT '0',
    `name` VARCHAR(50) NOT NULL DEFAULT '0' COLLATE 'utf8mb4_general_ci',
    `optional` VARCHAR(50) NOT NULL DEFAULT '0' COLLATE 'utf8mb4_general_ci',
    `opt` VARCHAR(50) NOT NULL DEFAULT '0' COLLATE 'utf8mb4_general_ci',
    `level` INT(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=512
;

学生期末考试成绩表

s_order:成绩校排名 f_order:成绩方向校排名

CREATE TABLE `g_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,
    `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=4607
;

学生中考成绩表

CREATE TABLE `s_score` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `num` CHAR(8) NOT NULL DEFAULT '0' COLLATE 'utf8mb4_general_ci',
  `class` INT(11) NOT NULL DEFAULT '0',
  `name` VARCHAR(50) NOT NULL DEFAULT '0' COLLATE 'utf8mb4_general_ci',
  `optional` VARCHAR(50) NOT NULL DEFAULT '0' COLLATE 'utf8mb4_general_ci',
  `opt` VARCHAR(50) NOT NULL DEFAULT '0' COLLATE 'utf8mb4_general_ci',
  `score` FLOAT NOT NULL DEFAULT '0',
  `type` VARCHAR(50) NOT NULL DEFAULT '0' COLLATE 'utf8mb4_general_ci',
  `s_order` INT(11) NULL DEFAULT NULL,
  `f_order` INT(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=5111
;

二、部分统计方法

线下生本科线

SELECT i.num,i.class,i.name,i.optional,i.opt,f.score FROM info i,finalscore f WHERE i.num=f.num AND f.score>=400 AND i.level=0;

线下生高于平均分数

新建均分变量:SET @jf:=(select round(AVG(score),1) FROM finalscore); 大于均分:f.score>=@jf

SET @jf:=(select round(AVG(score),1) FROM finalscore);
SELECT i.num,i.class,i.name,i.optional,i.opt,f.score FROM info i,finalscore f WHERE i.num=f.num AND f.score>=@jf AND i.level=0 ORDER BY f.score desc,i.class;

线下生高于平均分数（分物理和历史组合）

同上

SET @jf:=(select round(AVG(score),1) FROM finalscore WHERE opt='物理');
SELECT i.num,i.class,i.name,i.optional,i.opt,f.score FROM info i,finalscore f WHERE i.num=f.num AND f.score>=@jf AND i.level=0 AND f.opt='物理' ORDER BY f.score desc,i.class;

根据信息名单，合并中考与期末分数

使用子查询联合两个分数表(SELECT score FROM score f WHERE f.num=i.num AND f.type='语文') f_score

SELECT i.num,i.class,i.name,i.optional,i.opt,i.level,(SELECT score FROM finalscore f WHERE f.num=i.num) f_score,(SELECT score FROM s_score s WHERE s.num=i.num AND s.type='总分') s_score FROM info i ORDER BY class asc,f_score desc;

根据信息名单，合并单科中考与期末分数

使用子查询联合两个分数表(SELECT score FROM score f WHERE f.num=i.num AND f.type='语文') f_score 这种方式感觉不好，不如join方便，查询的字段无法在where中使用

SELECT i.num,i.class,i.name,i.optional,i.opt,i.level,(SELECT score FROM score f WHERE f.num=i.num AND f.type='语文') f_score,(SELECT score FROM s_score s WHERE i.num=s.num AND s.type='语文') s_score FROM info i ORDER BY class asc,f_score desc;

计算方向、单科成绩排名
- 计算排名：(rank() over(order BY s.score DESC)) as pm
- rank()是窗口函数

SELECT s.num,s.class,s.name,s.score,(rank() over(order BY s.score DESC)) as pm FROM s_score s WHERE s.type='数学' AND s.opt='物理';

通过临时表，更新总分成绩排名
- 创建临时表:CREATE TEMPORARY table tmp_score as SELECT
- 更新数据:UPDATE s_score SET s_order 有9大主科，每次写一个SQL就非常不方便，用Python实现就比较容易

DROP TEMPORARY TABLE IF EXISTS tmp_score;
CREATE TEMPORARY table tmp_score as SELECT s.num,s.class,s.name,s.score,(rank() over(order BY s.score DESC)) as s_order FROM s_score s WHERE s.type='总分';
UPDATE s_score SET s_order=(SELECT s_order FROM tmp_score WHERE tmp_score.num=s_score.num AND s_score.type='总分')

根据信息名单，合并中考、期末分数、排名等
- info是主表使用左联合,查询中考和期末考试成绩left JOIN g_score g ON i.num=g.num AND i.optional=g.optional AND g.type='总分'
- 联合查询有个好处,处理多个表的多个字段更容易.而子查询则比较复杂

SELECT i.class,i.name,i.optional,i.level,g.score,g.origin_score,g.s_order,g.f_order,s.score,s.s_order,s.f_order FROM info i left JOIN g_score g ON i.num=g.num AND i.optional=g.optional AND g.type='总分' LEFT JOIN s_score s ON i.num=s.num AND i.optional=s.optional AND s.`type`='总分' ORDER BY class,g.score desc

统计进步前50名
- 按照期末、中考方向相差最大的排名排序：ORDER BY s.f_order-g.f_order desc
- 取前50名：LIMIT 50

SELECT i.class,i.name,i.optional,i.level,g.score,g.origin_score,g.s_order,g.f_order,s.score,s.s_order,s.f_order FROM info i left JOIN g_score g ON i.num=g.num AND i.optional=g.optional AND g.type='总分' LEFT JOIN s_score s ON i.num=s.num AND i.optional=s.optional AND s.`type`='总分' ORDER BY s.f_order-g.f_order DESC LIMIT 50

MySQL分析学生中考和期末联考成绩

MySQL分析学生中考和期末联考成绩

一、表结构

二、部分统计方法

results matching ""

No results matching ""

results matching ""

No results matching ""