咨询SQL Server学生月度统计数据插入与更新的最优实现方案

不需要先插入基础数据再逐行更新，一次性插入带计算字段的数据是更优的实现逻辑——它能减少数据库IO操作次数，避免中间状态的数据不一致问题，性能更高效。

一、最优方案：一次性插入所有字段 #

直接关联所有数据源，在插入时完成Grade1、Grade2、Absences的计算，无需后续更新操作。以下是示例代码（假设成绩表为Grades、考勤表为Attendance，需根据实际表结构调整）：

declare @maindate date = '20230101';
declare @targetYear int = DATEPART(YEAR, @maindate);
declare @targetMonth int = DATEPART(MONTH, @maindate);

insert into Monthly_Stats (StudentID, Year, Month, Grade1, Grade2, Absences)
select
    sa.StudentID,
    sa.AllocatedYear,
    sa.AllocatedMonth,
    -- 计算Grade1：当月科目1的平均成绩（可根据需求改为单次考试成绩）
    COALESCE(g1.AvgScore, 0) as Grade1,
    -- 计算Grade2：当月科目2的平均成绩
    COALESCE(g2.AvgScore, 0) as Grade2,
    -- 计算缺勤次数：当月标记为缺勤的记录总数
    COALESCE(a.AbsenceCount, 0) as Absences
from Students_Allocation sa
-- 左连接科目1的成绩统计
left join (
    select
        StudentID,
        AVG(Score) as AvgScore
    from Grades
    where DATEPART(YEAR, ExamDate) = @targetYear
      and DATEPART(MONTH, ExamDate) = @targetMonth
      and SubjectID = 1 -- 替换为Grade1对应的科目标识
    group by StudentID
) g1 on sa.StudentID = g1.StudentID
-- 左连接科目2的成绩统计
left join (
    select
        StudentID,
        AVG(Score) as AvgScore
    from Grades
    where DATEPART(YEAR, ExamDate) = @targetYear
      and DATEPART(MONTH, ExamDate) = @targetMonth
      and SubjectID = 2 -- 替换为Grade2对应的科目标识
    group by StudentID
) g2 on sa.StudentID = g2.StudentID
-- 左连接考勤统计
left join (
    select
        StudentID,
        COUNT(*) as AbsenceCount
    from Attendance
    where DATEPART(YEAR, AttendanceDate) = @targetYear
      and DATEPART(MONTH, AttendanceDate) = @targetMonth
      and IsAbsent = 1 -- 替换为缺勤状态的标识
    group by StudentID
) a on sa.StudentID = a.StudentID
where sa.AllocatedMonth = @targetMonth
  and sa.AllocatedYear = @targetYear
  and sa.Active = 1;

关键说明： #

• 使用COALESCE处理无成绩/考勤的学生，避免插入NULL值；
• 成绩计算逻辑可根据实际需求调整（比如取当月最新考试成绩、最高分等）；
• 若Monthly_Stats存在StudentID+Year+Month的主键，可添加IF NOT EXISTS或改用MERGE语句，避免重复插入。

二、备选方案：先插基础数据再批量更新 #

如果业务场景要求必须分两步操作，不要逐行更新，应使用批量更新提升性能：

declare @maindate date = '20230101';
declare @targetYear int = DATEPART(YEAR, @maindate);
declare @targetMonth int = DATEPART(MONTH, @maindate);

-- 1. 插入基础数据
insert into Monthly_Stats (StudentID, Year, Month)
select StudentID, AllocatedYear, AllocatedMonth
from Students_Allocation
where AllocatedMonth = @targetMonth
  and AllocatedYear = @targetYear
  and Active = 1;

-- 2. 批量更新成绩字段
update ms
set
    Grade1 = COALESCE(g1.AvgScore, 0),
    Grade2 = COALESCE(g2.AvgScore, 0)
from Monthly_Stats ms
left join (
    select
        StudentID,
        AVG(Score) as AvgScore
    from Grades
    where DATEPART(YEAR, ExamDate) = @targetYear
      and DATEPART(MONTH, ExamDate) = @targetMonth
      and SubjectID = 1
    group by StudentID
) g1 on ms.StudentID = g1.StudentID
left join (
    select
        StudentID,
        AVG(Score) as AvgScore
    from Grades
    where DATEPART(YEAR, ExamDate) = @targetYear
      and DATEPART(MONTH, ExamDate) = @targetMonth
      and SubjectID = 2
    group by StudentID
) g2 on ms.StudentID = g2.StudentID
where ms.Year = @targetYear
  and ms.Month = @targetMonth;

-- 3. 批量更新缺勤字段
update ms
set
    Absences = COALESCE(a.AbsenceCount, 0)
from Monthly_Stats ms
left join (
    select
        StudentID,
        COUNT(*) as AbsenceCount
    from Attendance
    where DATEPART(YEAR, AttendanceDate) = @targetYear
      and DATEPART(MONTH, AttendanceDate) = @targetMonth
      and IsAbsent = 1
    group by StudentID
) a on ms.StudentID = a.StudentID
where ms.Year = @targetYear
  and ms.Month = @targetMonth;

注意事项： #

• 该方案会增加数据库操作次数，性能弱于一次性插入；
• 若存在并发场景，可能出现插入后未更新的中间数据被读取的情况，需考虑事务控制。

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