高考相关数据分析——考生同分排位规则与数据库SQL实现-CSDN博客

本文链接：https://blog.csdn.net/weixin_39534833/article/details/146804854

引言

大家知道高考最重要的是成绩，有成绩做基础，填报志愿录取大学等，就有了依据。不过，比成绩更直观的其实是成绩对应的排位，谁的位次更靠前，谁就拥有更好的选择。说到这里，我抛出一个问题考考大家，如果张三、李四两位福建考生的高考总分都是600分，位次是并列的吗？
在这里插入图片描述

1. 规则要保证逻辑的合理性

小明：L老师，我觉得总分一样，那位次自然就是一样的。

L：大家看看2024年福建高考（物理组）一分一段表，如下所示。可以看出每一位考生都有同分的情况存在，少则2人，多则700多人，也就是说同分是非常普遍的情况。此时我们假设一种情况，某专业录取仅剩1个名额，多个同分的人恰好都报这个专业，争抢该名额，该怎么筛选呢？

在这里插入图片描述

大家知道高考最重要的是成绩，有成绩做基础，填报志愿录取大学等，就有了依据。不过，比成绩更直观的其实是成绩对应的排位，谁的位次更靠前，谁就拥有更好的选择。说到这里，我抛出一个问题考考大家，如果张三、李四两位福建考生的高考总分都是600分，位次是并列的吗？

小明：L老师，可以让学校来筛选啊，比如看其特长或专业相关的学科成绩高低？

L：这需要反复确认，效率太低了，而且也带来很大的不透明性，弊端很明显啊。如果做到同分还能分出位次，学校就少了很多工作量了，合理多了。

小红：L老师，我曾听某自媒体说过这事，知道存在同分不同位次的情况。好像物理类是按照语、数、英、物的分数高低依次比较，直到分出高低来。

L：不对！

小红：啊？

L：你们做事缺乏严谨性。在企业，如果某关键项目的相关需求、算法依据并非来源于一手官方信息，则该项目必定无法通过评审。作为严肃的高考，成绩位次的算法必定是可以在相关的官方平台找到依据的。

小红：老师，那每个省份都一样吗？

L：每个省份的情况不尽相同，需要自己去调研。福建省是依据《福建省高等学校招生委员会福建省教育厅关于印发2024年福建省普通高等学校招生录取实施办法的通知》的第五章“成绩排序规则”进行的，如下所示。

在这里插入图片描述

小明：原来如此。

2. 排位算法

L：依据规则，位次算法可解读为法

依次按语文与数学两科成绩之和、语文或数学单科最高成绩、外语成绩、首选科目（物理或历史）成绩进行依次比拼。如果还分不出胜负，则继续PK选课（生物、化学、地理、政治）。依次为选科的单科最高成绩、次高成绩。如果在如此极端的情况下，还能出现位次相同，那就是天意了。

-- 考生成绩表（区分文理科）

CREATE TABLE students (

    student_id   INT PRIMARY KEY,     -- 考生号（唯一标识）

    name         VARCHAR(50),         -- 姓名

    category     CHAR(1) NOT NULL,    -- 科目类别：'P'物理类（理科）/ 'H'历史类（文科）

    chinese      INT NOT NULL,        -- 语文成绩

    math         INT NOT NULL,        -- 数学成绩

    english      INT NOT NULL,        -- 外语成绩

    primary_sub  INT NOT NULL,        -- 首选科目成绩：物理或历史

    elective1    INT NOT NULL,        -- 再选科目1（如化学、地理）

    elective2    INT NOT NULL         -- 再选科目2（如生物、政治）

);

设计要点：

最小化字段：仅存储原始成绩，动态计算组合分（如总分、语文数学之和等）。

通用性：category 字段明确区分文理科，primary_sub 动态表示物理或历史成绩。

约束：所有成绩字段设为 NOT NULL，避免空值干扰排序逻辑。

物理类考生（理科）插入数据


INSERT INTO students  VALUES

-- 案例1: 总分相同，语文+数学之和不同

(1, '张三', 'P', 120, 130, 125, 85, 90, 95),  -- 语文+数学=250

(2, '李四', 'P', 125, 125, 125, 85, 95, 90),  -- 语文+数学=250（同和，继续比拼单科）

-- 案例2: 语文+数学之和相同，单科最高分不同

(3, '王五', 'P', 115, 135, 130, 90, 85, 80),  -- 语文+数学=250，数学135（单科最高）

(4, '赵六', 'P', 130, 120, 120, 80, 95, 90),  -- 语文+数学=250，语文130（单科最高）

-- 案例3: 完全同分（极端情况）

(5, '周七', 'P', 120, 130, 125, 85, 90, 95),  -- 与张三完全同分

-- 案例4: 再选科目决定排名

(6, '吴八', 'P', 120, 130, 125, 85, 88, 97),  -- 再选科目最高97 > 张三的95

(7, '郑九', 'P', 120, 130, 125, 85, 95, 88);  -- 再选科目最高95 = 张三，次高88 < 张三的90

物理类考生排名（展示关键字段）

   SELECT 
    student_id AS 考生号,

    name AS 姓名,

    chinese AS 语文,

    math AS 数学,

    (chinese + math) AS 语文数学总分,  -- 显式展示语文+数学总分

    english AS 英语,

    primary_sub AS 物理,

    elective1 AS 再选1,

    elective2 AS 再选2,

    total_score AS 总分,

    ROW_NUMBER() OVER (

        ORDER BY 

            total_score DESC,

            (chinese + math) DESC,

            GREATEST(chinese, math) DESC,

            english DESC,

            primary_sub DESC,

            GREATEST(elective1, elective2) DESC,

            LEAST(elective1, elective2) DESC

    ) AS 理科排名

FROM (

    SELECT 

        *,

        (chinese + math + english + primary_sub + elective1 + elective2) AS total_score

    FROM students1

    WHERE category = 'P'

) AS physics_students;

结果：

student_id	name	chinese	math	english	physics	elective1	elective2	total_score	physics_rank
5	        周七      120	130	     125	 85	      90	       95	     645	       1
1	        张三    	 120	130	     125	 85	      90           95	     645	       2
2	        李四	     125	125	     125	 85	      95	       90	     645	       3
3	        王五	     115	135	     130	 90	      85	       80	     635	       4
4	        赵六	     130	120	     120	 80	      95	       90	     635           5