Access SQL：按教师分组统计各月份学生缺勤天数

嘿，我来帮你搞定这个全月份缺勤统计的需求！针对你已经完成1月份统计的情况，我们可以通过拆分跨月缺勤记录+生成全月份辅助列表的方式，实现按教师维度的12个月完整统计。

核心要解决两个问题：一是把跨月的缺勤记录正确拆分到对应月份，计算实际缺勤天数；二是确保每个教师的12个月份都有统计结果（哪怕缺勤天数为0）。

步骤1：生成1-12月的辅助月份列表 #

先通过UNION语句生成所有月份的基础列表，这能保证结果里每个月份都不会缺失：

SELECT 1 AS 月份 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 UNION ALL
SELECT 10 UNION ALL
SELECT 11 UNION ALL
SELECT 12

步骤2：拆分缺勤记录到对应月份并计算天数 #

接下来关联学生表、缺勤记录表和月份列表，计算每条缺勤记录在每个月份的实际缺勤天数（比如12月30日到1月2日的缺勤，会拆分到12月和1月分别计算）：

SELECT
    s.教师姓名,
    m.月份,
    SUM(
        IIF(
            Max(ae.缺勤起始日期, DateSerial(Year(ae.缺勤起始日期), m.月份, 1)) 
            <= Min(ae.缺勤结束日期, DateSerial(Year(ae.缺勤起始日期), m.月份 + 1, 0)),
            DateDiff("d", 
                Max(ae.缺勤起始日期, DateSerial(Year(ae.缺勤起始日期), m.月份, 1)),
                Min(ae.缺勤结束日期, DateSerial(Year(ae.缺勤起始日期), m.月份 + 1, 0))
            ) + 1,
            0
        )
    ) AS 当月缺勤天数
FROM
    Students s
INNER JOIN
    [Absence Extract] ae ON s.学生ID = ae.学生ID,
    (
        SELECT 1 AS 月份 UNION ALL
        SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
        SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
        SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL
        SELECT 11 UNION ALL SELECT 12
    ) AS m
GROUP BY
    s.教师姓名, m.月份

步骤3：补全教师无缺勤的月份（显示0天） #

上面的查询只会返回有缺勤记录的教师-月份组合，如果需要显示所有教师的所有月份（包括缺勤天数为0的情况），用左连接补全即可：

-- 先获取所有教师的唯一列表
WITH AllTeachers AS (
    SELECT DISTINCT 教师姓名 FROM Students
),
-- 生成所有教师+所有月份的完整组合
TeacherMonths AS (
    SELECT
        at.教师姓名,
        m.月份
    FROM
        AllTeachers at,
        (
            SELECT 1 AS 月份 UNION ALL
            SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
            SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
            SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL
            SELECT 11 UNION ALL SELECT 12
        ) AS m
),
-- 计算有缺勤的教师-月份天数
AbsenceStats AS (
    SELECT
        s.教师姓名,
        m.月份,
        SUM(
            IIF(
                Max(ae.缺勤起始日期, DateSerial(Year(ae.缺勤起始日期), m.月份, 1)) 
                <= Min(ae.缺勤结束日期, DateSerial(Year(ae.缺勤起始日期), m.月份 + 1, 0)),
                DateDiff("d", 
                    Max(ae.缺勤起始日期, DateSerial(Year(ae.缺勤起始日期), m.月份, 1)),
                    Min(ae.缺勤结束日期, DateSerial(Year(ae.缺勤起始日期), m.月份 + 1, 0))
                ) + 1,
                0
            )
        ) AS 当月缺勤天数
    FROM
        Students s
    INNER JOIN
        [Absence Extract] ae ON s.学生ID = ae.学生ID,
        (
            SELECT 1 AS 月份 UNION ALL
            SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
            SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
            SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL
            SELECT 11 UNION ALL SELECT 12
        ) AS m
    GROUP BY
        s.教师姓名, m.月份
)
-- 左连接补全0天记录，确保每个教师每个月份都有结果
SELECT
    tm.教师姓名,
    tm.月份,
    Nz(as_.当月缺勤天数, 0) AS 当月缺勤天数
FROM
    TeacherMonths tm
LEFT JOIN
    AbsenceStats as_ ON tm.教师姓名 = as_.教师姓名 AND tm.月份 = as_.月份
ORDER BY
    tm.教师姓名, tm.月份

小提示 #

• 如果你的缺勤记录涉及跨年，上面的查询默认按缺勤起始日期的年份统计。如果需要按自然年分组，只需在查询中添加年份字段的筛选或分组即可。
• 确保Students和[Absence Extract]表的关联字段（比如学生ID）是匹配的，要是字段名不一样，记得修改JOIN条件。

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