如何在SQL Server中每组最后一条记录后插入新行

先纠正你现有查询的问题 #

你写的获取组内最后一条记录的查询存在两处错误：

1. CTE名称为new_students_table，但查询时误用了ranked_messages，属于表名笔误；
2. ORDER BY Subject DESC是按分组字段排序，组内所有行的Subject值一致，导致每行的student行号都为1，无法正确筛选出组内最后一条记录。

修正后的查询（假设表中有可确定记录顺序的字段，比如StudentID）：

WITH new_students_table AS 
(
    SELECT 
        m.*, 
        ROW_NUMBER() OVER (PARTITION BY Subject ORDER BY StudentID DESC) AS student
    FROM 
        students AS m
)
SELECT * 
FROM new_students_table 
WHERE student = 1;

实现预期输出的两种方案 #

要在每组数据后插入总分行，可通过UNION ALL合并原始数据与分组求和结果，再排序让总分行落在对应组的最后：

方案1：总分行的Student字段显示为"Total"

-- 原始数据
SELECT 
    Student,
    Subject,
    Score,
    1 AS sort_order
FROM students
UNION ALL
-- 各学科总分行
SELECT 
    'Total' AS Student,
    Subject,
    SUM(Score) AS Score,
    2 AS sort_order
FROM students
GROUP BY Subject
-- 可选：追加所有学科的总计行
UNION ALL
SELECT 
    'Grand Total' AS Student,
    NULL AS Subject,
    SUM(Score) AS Score,
    3 AS sort_order
FROM students
-- 排序确保总分行在对应学科的最后
ORDER BY 
    Subject,
    sort_order;

方案2：总分行沿用组内最后一条记录的Student字段

如果需要总分行的Student字段与对应学科最后一条记录的Student一致，可结合排序查询与分组求和：

WITH ranked_students AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY Subject ORDER BY StudentID DESC) AS rn
    FROM students
),
subject_totals AS (
    SELECT 
        Subject,
        SUM(Score) AS TotalScore
    FROM students
    GROUP BY Subject
)
-- 原始数据
SELECT 
    Student,
    Subject,
    Score,
    1 AS sort_order
FROM students
UNION ALL
-- 各学科总分行，使用组内最后一条记录的Student
SELECT 
    rs.Student,
    rs.Subject,
    st.TotalScore AS Score,
    2 AS sort_order
FROM ranked_students rs
JOIN subject_totals st ON rs.Subject = st.Subject
WHERE rs.rn = 1
-- 可选：追加总计行
UNION ALL
SELECT 
    'Grand Total' AS Student,
    NULL AS Subject,
    SUM(Score) AS Score,
    3 AS sort_order
FROM students
ORDER BY 
    Subject,
    sort_order;

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