优化SQL查询以获取含未尝试学生的SCORM完成状态

当前查询语句（中文翻译） #

SELECT 
    u.id AS userid,
    u.firstname AS firstname,
    u.lastname AS lastname,
    c.fullname AS coursename,
    s.name AS scormname,
    sst.attempt AS attempt,
    sst.value AS status,
    CASE
        WHEN s.timeopen = 0 THEN '未启用'
        WHEN s.timeopen <> 0 THEN FROM_UNIXTIME(s.timeopen, '%d-%b-%Y')
    END AS starttime,
    CASE
        WHEN s.timeclose = 0 THEN '未启用'
        WHEN s.timeclose <> 0 THEN FROM_UNIXTIME(s.timeclose, '%d-%b-%Y')
    END AS enddate
FROM mdl_scorm_scoes_track sst
JOIN mdl_scorm s ON s.id = sst.scormid
JOIN mdl_course c ON c.id = s.course
JOIN mdl_user u ON sst.userid = u.id
WHERE sst.value IN ('incomplete', 'completed')

修改后的查询语句（包含未尝试SCORM的选课学生） #

SELECT 
    u.id AS userid,
    u.firstname AS firstname,
    u.lastname AS lastname,
    c.fullname AS coursename,
    s.name AS scormname,
    COALESCE(sst.attempt, 0) AS attempt, -- 无尝试记录时显示0
    CASE
        WHEN sst.value IS NULL THEN '未尝试'
        ELSE sst.value
    END AS status,
    CASE
        WHEN s.timeopen = 0 THEN '未启用'
        ELSE FROM_UNIXTIME(s.timeopen, '%d-%b-%Y')
    END AS starttime,
    CASE
        WHEN s.timeclose = 0 THEN '未启用'
        ELSE FROM_UNIXTIME(s.timeclose, '%d-%b-%Y')
    END AS enddate
FROM mdl_course c
-- 关联课程下的SCORM包
JOIN mdl_scorm s ON s.course = c.id
-- 关联选课记录：获取所有选了该课程的学生
JOIN mdl_enrol e ON e.courseid = c.id
JOIN mdl_user_enrolments ue ON ue.enrolid = e.id
JOIN mdl_user u ON u.id = ue.userid
-- 左连接SCORM跟踪表，保留所有选课学生，无论是否有尝试记录
LEFT JOIN mdl_scorm_scoes_track sst 
    ON sst.scormid = s.id 
    AND sst.userid = u.id 
    AND sst.value IN ('incomplete', 'completed')
-- 可选：同一学生同一SCORM多条记录时去重，可根据需求调整逻辑
GROUP BY u.id, s.id, c.id
ORDER BY c.fullname, u.lastname, u.firstname

关键修改说明 #

• 关联逻辑调整：从课程表出发，通过选课关联表（mdl_enrol、mdl_user_enrolments）获取所有选课学生，确保不会遗漏未尝试SCORM的用户。
• 左连接跟踪表：用LEFT JOIN替代原有的内连接，保留所有选课学生数据，即使他们没有SCORM尝试记录。
• 状态字段补全：通过CASE语句给无跟踪记录的学生标记为未尝试，统一状态展示。
• 尝试次数处理：用COALESCE将无尝试记录的attempt字段设为0，保证数据格式一致性。
• 可选去重：若同一学生同一SCORM存在多条跟踪记录，可通过GROUP BY或添加排序逻辑（如取最新尝试记录）优化结果。

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