如何编写SQL实现指定列求和排名及学生多考试成绩汇总查询

嘿，我来帮你搞定这两个SQL需求，都是日常数据分析里很常见的场景，咱们一个个拆解：

要实现指定列求和+排名，核心是把聚合求和和窗口排名函数结合起来，不同数据库的语法基本通用，下面给你分场景举例：

场景示例 #

假设我们有一张sales表，存储各区域的销售数据，字段包括region（区域名称）、sale_amount（单条销售额）。我们要计算每个区域的总销售额，再按总销售额从高到低排名。

主流数据库写法（MySQL 8.0+/PostgreSQL/SQL Server/Oracle） #

这些数据库支持窗口函数，用RANK()或DENSE_RANK()就能轻松实现：

SELECT 
    region,
    SUM(sale_amount) AS total_sales,
    -- RANK()会跳号：若两个区域销售额相同，下一个排名会跳过序号
    RANK() OVER(ORDER BY SUM(sale_amount) DESC) AS sales_rank,
    -- DENSE_RANK()不跳号：相同销售额的区域排名一致，后续排名连续
    DENSE_RANK() OVER(ORDER BY SUM(sale_amount) DESC) AS dense_sales_rank
FROM sales
GROUP BY region
ORDER BY total_sales DESC;

老版本MySQL写法（无窗口函数） #

如果用的是MySQL 5.x及以下版本，没有窗口函数，可以用子查询+变量来实现排名：

SELECT 
    region,
    total_sales,
    @rank := @rank + 1 AS sales_rank
FROM (
    -- 先分组计算总销售额并排序
    SELECT region, SUM(sale_amount) AS total_sales
    FROM sales
    GROUP BY region
    ORDER BY total_sales DESC
) AS ranked_sales,
-- 初始化排名变量
(SELECT @rank := 0) AS rank_init;

针对mark_summery表的需求，关键是先筛选出该学生的最多四次考试记录（需要一个排序字段来确定哪四次，比如考试日期exam_date或考试IDexam_id），再对这部分记录的两个字段分别求和。

单学生场景（指定某一个学生） #

假设我们要查询学生ID为S123的最新四次考试成绩总和：

SELECT 
    student_id,
    SUM(obt_marks) AS total_obt_marks, -- 四次考试的得分总和
    SUM(total_marks) AS total_total_marks -- 四次考试的总分总和
FROM (
    -- 先筛选该学生的记录，按考试日期倒序取最新4条
    SELECT 
        student_id,
        obt_marks,
        total_marks
    FROM mark_summery
    WHERE student_id = 'S123' -- 替换成你要查询的学生ID
    ORDER BY exam_date DESC -- 若要取最早四次，改成ASC
    LIMIT 4 -- 限制最多4条记录
) AS student_top4_exams;

多学生场景（每个学生取最多四次） #

如果需要批量处理所有学生，每个学生都取最多四次考试的成绩总和，可以用ROW_NUMBER()窗口函数来标记考试序号：

SELECT 
    student_id,
    SUM(obt_marks) AS total_obt_marks,
    SUM(total_marks) AS total_total_marks
FROM (
    SELECT 
        student_id,
        obt_marks,
        total_marks,
        -- 给每个学生的考试按日期排序编号，最新的为1
        ROW_NUMBER() OVER(PARTITION BY student_id ORDER BY exam_date DESC) AS exam_seq
    FROM mark_summery
) AS student_exams
WHERE exam_seq <= 4 -- 只保留前四次考试
GROUP BY student_id;

注意：如果学生的考试次数不足四次，上述SQL会自动取该学生所有的考试记录求和，完全符合“最多四次”的要求。

内容的提问来源于stack exchange，提问作者Ijaz Sunny