SQL动态生成列行求和：如何在考勤报表存储过程中添加Total列

嘿，我明白你现在的困扰：想给班级月度考勤报表加个Total列，统计学生在指定日期范围内的考勤总和，但又怕直接用sum(Attendance)会把表中所有日期的记录都算进去。其实你的思路方向是对的，只是需要注意几个细节，我们分两种常见场景来处理：

先搞清楚核心问题 #

你提到的“sum会计算所有日期”，大概率是担心动态SQL里的日期过滤条件没生效，或者你希望把那些没有考勤记录的非周日日期也按0计入总和（毕竟报表里列了这些日期，总和应该对应这些列的数值）。我们分别解决这两种情况：

场景1：只统计有考勤记录的指定日期总和 #

你的原代码里其实已经加了A.Date between @startdate and @enddate的过滤，理论上sum(Attendance)只会计算这个范围内的数值。但你的动态SQL存在两个小问题：一是直接拼接字符串有SQL注入风险，二是日期格式拼接可能出错。我帮你改成参数化的版本，同时确保总和计算正确：

CREATE PROCEDURE GET_ATTENDANCE_REPORT_FOR_FACULTY 
    @startdate DATE, 
    @enddate DATE, 
    @coursecode nvarchar(10), 
    @subjectcode nvarchar(10) 
AS 
BEGIN 
    SET NOCOUNT ON;
    DECLARE @query as nvarchar(MAX);
    
    -- 生成指定范围内的非周日日期列表
    WITH cte (startdate) AS (
        SELECT @startdate startdate 
        UNION ALL 
        SELECT DATEADD(DD, 1, startdate) 
        FROM cte 
        WHERE startdate < @enddate
    )
    -- 动态生成每个日期对应的考勤列
    SELECT @query = COALESCE(@query, '') + N',COALESCE(MAX(CASE WHEN A.[Date] = ''' + CAST(cte.startdate AS nvarchar(20)) + N''' THEN CONVERT(varchar(10),A.[Attendance]) END), ''NA'') ' + QUOTENAME(CONVERT(char(6), cte.startdate, 106))
    FROM cte 
    WHERE DATENAME(weekday, cte.startdate) <> 'Sunday';

    -- 拼接主查询，加入Total列，改用参数化避免注入
    SET @query = N'
        SELECT 
            S.RollNo AS [Roll No],
            CONCAT(U.FirstName, '' '', U.LastName) AS Name' 
            + @query + N',
            SUM(A.Attendance) AS Total  -- 仅统计指定范围内有记录的考勤总和
        FROM Attendance A
        JOIN Student S ON A.EnrollmentNo = S.EnrollmentNo
        JOIN UserDetails U ON S.EnrollmentNo = U.userID
        WHERE A.CourseCode = @coursecode 
          AND A.SubjectCode = @subjectcode 
          AND A.Date BETWEEN @startdate AND @enddate
        GROUP BY S.RollNo, U.FirstName, U.LastName';

    -- 执行参数化动态SQL，传递参数
    EXEC sp_executesql @query, 
        N'@startdate DATE, @enddate DATE, @coursecode nvarchar(10), @subjectcode nvarchar(10)',
        @startdate = @startdate,
        @enddate = @enddate,
        @coursecode = @coursecode,
        @subjectcode = @subjectcode;
END

场景2：统计所有非周日日期的总和（无记录按0算） #

如果你的报表里列了每个非周日的日期，那总和应该对应这些列的数值——哪怕某个学生当天没考勤记录（显示NA），也要按0计入总和。这时候就需要先把学生和所有非周日日期做关联，再左连接考勤表：

CREATE PROCEDURE GET_ATTENDANCE_REPORT_FOR_FACULTY 
    @startdate DATE, 
    @enddate DATE, 
    @coursecode nvarchar(10), 
    @subjectcode nvarchar(10) 
AS 
BEGIN 
    SET NOCOUNT ON;
    DECLARE @query as nvarchar(MAX);
    
    -- 生成指定范围内的非周日日期列表
    WITH date_cte (att_date) AS (
        SELECT @startdate att_date 
        UNION ALL 
        SELECT DATEADD(DD, 1, att_date) 
        FROM date_cte 
        WHERE att_date < @enddate
    ),
    -- 生成所有选该课程的学生 + 所有非周日日期的基础数据集
    student_dates AS (
        SELECT 
            S.RollNo,
            U.FirstName,
            U.LastName,
            S.EnrollmentNo,
            dc.att_date
        FROM Student S
        JOIN UserDetails U ON S.EnrollmentNo = U.userID
        CROSS JOIN date_cte dc
        WHERE EXISTS (
            -- 过滤出选了当前课程和科目的学生
            SELECT 1 FROM Attendance A 
            WHERE A.EnrollmentNo = S.EnrollmentNo 
              AND A.CourseCode = @coursecode 
              AND A.SubjectCode = @subjectcode
        )
        AND DATENAME(weekday, dc.att_date) <> 'Sunday'
    )
    -- 动态生成每个日期对应的考勤列
    SELECT @query = COALESCE(@query, '') + N',COALESCE(MAX(CASE WHEN sd.att_date = ''' + CAST(dc.att_date AS nvarchar(20)) + N''' THEN CONVERT(varchar(10),A.[Attendance]) END), ''NA'') ' + QUOTENAME(CONVERT(char(6), dc.att_date, 106))
    FROM date_cte dc 
    WHERE DATENAME(weekday, dc.att_date) <> 'Sunday';

    -- 拼接主查询，计算所有非周日日期的总和（无记录按0）
    SET @query = N'
        SELECT 
            sd.RollNo AS [Roll No],
            CONCAT(sd.FirstName, '' '', sd.LastName) AS Name' 
            + @query + N',
            SUM(COALESCE(A.Attendance, 0)) AS Total  -- 没考勤记录的日期按0算总和
        FROM student_dates sd
        LEFT JOIN Attendance A 
            ON sd.EnrollmentNo = A.EnrollmentNo 
            AND sd.att_date = A.Date
            AND A.CourseCode = @coursecode 
            AND A.SubjectCode = @subjectcode
        GROUP BY sd.RollNo, sd.FirstName, sd.LastName';

    -- 执行参数化动态SQL
    EXEC sp_executesql @query, 
        N'@startdate DATE, @enddate DATE, @coursecode nvarchar(10), @subjectcode nvarchar(10)',
        @startdate = @startdate,
        @enddate = @enddate,
        @coursecode = @coursecode,
        @subjectcode = @subjectcode;
END

几个重要的改进点 #

• 参数化动态SQL：彻底避免SQL注入，同时解决日期格式拼接可能出现的错误（比如不同语言环境下的日期格式）。
• 显式JOIN替代隐式JOIN：原代码里的Attendance A, Student S, UserDetails U是旧的隐式连接写法，改成显式JOIN后代码可读性和维护性更好。
• 精准的范围控制：场景1利用WHERE条件过滤指定日期，场景2通过笛卡尔积确保所有报表里的日期都被计入总和，完全匹配你的需求。

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