求获取用户最新SCORM尝试完成状态的SQL查询语句

需求说明 #

需要获取用户SCORM课程的最新/最后一次尝试的完成状态，仅显示最新尝试的结果（如用户有多次尝试，只保留编号最大的那次状态），同时包含从未开始过SCORM课程的用户状态（标记为not_started）。

修改后的SQL查询语句 #

-- 先获取每个用户对应每个SCORM的最新尝试编号
WITH latest_attempts AS (
    SELECT userid, scormid, MAX(attempt) AS max_attempt
    FROM mdl_scorm_scoes_track
    WHERE element = 'cmi.core.lesson_status'
    GROUP BY userid, scormid
)
SELECT 
    u.firstname AS First, 
    u.lastname AS Last, 
    c.fullname AS Course, 
    sc.name AS scromname, 
    st.attempt AS Attempt, 
    st.value AS Status,
    FROM_UNIXTIME(st.timemodified,"%m-%d-%Y") AS Date 
FROM mdl_scorm_scoes_track AS st 
JOIN latest_attempts la ON st.userid = la.userid AND st.scormid = la.scormid AND st.attempt = la.max_attempt
JOIN mdl_user AS u ON st.userid = u.id
JOIN mdl_scorm AS sc ON sc.id = st.scormid
JOIN mdl_course AS c ON c.id = sc.course
WHERE st.element = 'cmi.core.lesson_status'

UNION

SELECT 
    user2.firstname AS First, 
    user2.lastname AS Last, 
    c.fullname AS Course, 
    sc.name AS scromname, 
    "-" AS Attempt, 
    "not_started" AS Status, 
    "-" AS Date
FROM mdl_user_enrolments AS ue
JOIN mdl_enrol AS e ON e.id = ue.enrolid
JOIN mdl_course AS c ON c.id = e.courseid
JOIN mdl_user AS user2 ON user2.id = ue.userid
JOIN mdl_scorm AS sc ON sc.course = c.id
LEFT JOIN mdl_scorm_scoes_track AS st ON st.scormid = sc.id AND st.userid = user2.id
WHERE st.timemodified IS NULL

ORDER BY Course, Last, First, Attempt;

关键修改点 #

• 使用CTE（latest_attempts）分组统计每个用户、每个SCORM的最大尝试编号，确保只获取最新的尝试记录
• 关联主表时通过userid、scormid和attempt = max_attempt筛选出最新尝试的状态数据
• 保留原有的UNION逻辑，继续获取未开始SCORM课程的用户状态数据

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