SQL联查学生与考勤表数据重复：如何筛选当日缺勤学生

重复数据原因 #

你的查询返回重复结果，核心问题是关联条件错误：你用班级ID（A.class_id=B.class_id）作为关联依据，这会让班级里的每个学生，都和该班级当天的每一条出勤记录进行匹配，最终每个学生出现的次数等于班级当天的出勤记录总数。

修正重复问题的查询 #

要正确关联学生和他们的出勤记录，必须用学生ID（student_id）作为关联条件，而不是班级ID。如果同一个学生当天有多次签到记录，还可以通过分组或窗口函数保留单条有效记录：

基础修正版（消除重复） #

SELECT A.student_name, A.student_phone, A.class_id, B.student_id, B.class_id, B.entry_at
FROM (
    SELECT student_id, student_name, student_phone, class_id 
    FROM `students` 
    WHERE company_id=1 AND class_id=1
) AS A 
LEFT JOIN (
    SELECT student_id, class_id, entry_at 
    FROM `attendances` 
    WHERE company_id=1 AND class_id=1 AND DATE(created_at)='2023-04-05'
) AS B 
ON A.student_id = B.student_id;

保留单条出勤记录（如最早签到） #

如果同一个学生当天有多条出勤记录，用窗口函数筛选出最早的一条：

SELECT student_name, student_phone, class_id, student_id, class_id, entry_at
FROM (
    SELECT 
        A.student_name, A.student_phone, A.class_id, 
        B.student_id, B.class_id, B.entry_at,
        ROW_NUMBER() OVER (PARTITION BY A.student_id ORDER BY B.entry_at) AS rn
    FROM (
        SELECT student_id, student_name, student_phone, class_id 
        FROM `students` 
        WHERE company_id=1 AND class_id=1
    ) AS A 
    LEFT JOIN (
        SELECT student_id, class_id, entry_at 
        FROM `attendances` 
        WHERE company_id=1 AND class_id=1 AND DATE(created_at)='2023-04-05'
    ) AS B 
    ON A.student_id = B.student_id
) AS temp
WHERE rn = 1;

筛选当日缺勤学生 #

要得到2023-04-05当日缺勤的学生，只需筛选出未匹配到出勤记录的学生（即B.student_id IS NULL的记录）：

SELECT A.student_name, A.student_phone, A.class_id
FROM (
    SELECT student_id, student_name, student_phone, class_id 
    FROM `students` 
    WHERE company_id=1 AND class_id=1
) AS A 
LEFT JOIN (
    SELECT student_id 
    FROM `attendances` 
    WHERE company_id=1 AND class_id=1 AND DATE(created_at)='2023-04-05'
) AS B 
ON A.student_id = B.student_id
WHERE B.student_id IS NULL;

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