SCORM交互数据查询异常：无法获取第三次交互值求助

问题描述 #

无法获取SCORM第三次交互的数值，查询结果显示的是第二次交互的数值，请问问题出在哪里？

原查询代码 #

SELECT scc.userid AS User, sct.name AS Name, scc.scoid AS SCORM_ID, sct.module AS Module, scc.value AS Touch_1, sct.value AS touch_2, sct.value AS Touch_3
FROM

(SELECT u.id AS userid, st.scoid AS scoid, st.value AS value

FROM prefix_course AS c

LEFT JOIN prefix_scorm AS sc ON sc.course=c.id

LEFT JOIN prefix_scorm_scoes_track AS st ON st.scormid=sc.id

LEFT JOIN prefix_user AS u ON u.id=st.userid

WHERE st.element='cmi.interactions_0.time') AS scc

LEFT JOIN

(SELECT c.fullname Course, sc.name AS module, st.scoid AS scoid, u.id AS userid, u.firstname name, st.attempt as Attempt, st.value AS value

FROM prefix_course AS c

LEFT JOIN prefix_scorm AS sc ON sc.course=c.id

LEFT JOIN prefix_scorm_scoes_track AS st ON st.scormid=sc.id

LEFT JOIN prefix_user AS u ON u.id=st.userid

WHERE st.element='cmi.interactions_1.time') AS sct 




ON sct.scoid=scc.scoid

AND sct.userid=scc.userid

ORDER BY scc.userid

问题分析 #

• 核心问题：原查询完全未处理第三次交互cmi.interactions_2.time的逻辑，Touch_3列直接复用了sct.value（对应第二次交互cmi.interactions_1.time的数值），因此无法获取第三次交互的数据。
• 次要问题：多次重复关联相同表，查询冗余且效率低下；字段别名混淆（如sct.name实际是用户名字，与模块名module命名逻辑不统一）。

修正方案 #

使用条件聚合简化查询，在单次表关联中直接提取三次交互的数值：

SELECT 
    u.id AS UserID,
    u.firstname AS Name,
    sc.id AS SCORM_ID,
    sc.name AS Module,
    -- 提取第一次交互时间
    MAX(CASE WHEN st.element = 'cmi.interactions_0.time' THEN st.value END) AS Touch_1,
    -- 提取第二次交互时间
    MAX(CASE WHEN st.element = 'cmi.interactions_1.time' THEN st.value END) AS Touch_2,
    -- 提取第三次交互时间
    MAX(CASE WHEN st.element = 'cmi.interactions_2.time' THEN st.value END) AS Touch_3
FROM prefix_course AS c
LEFT JOIN prefix_scorm AS sc ON sc.course = c.id
LEFT JOIN prefix_scorm_scoes_track AS st ON st.scormid = sc.id
LEFT JOIN prefix_user AS u ON u.id = st.userid
-- 过滤需要的交互元素
WHERE st.element IN ('cmi.interactions_0.time', 'cmi.interactions_1.time', 'cmi.interactions_2.time')
-- 按用户和SCORM实例分组，确保每个用户-课程组合仅返回一行
GROUP BY u.id, u.firstname, sc.id, sc.name
ORDER BY u.id;

修正说明 #

1. 新增对cmi.interactions_2.time的条件判断，直接获取第三次交互的数值，解决核心问题。
2. 仅通过一次表关联完成查询，避免原查询中子查询重复关联的冗余，提升查询效率。
3. 统一字段命名逻辑，避免别名混淆，增强可读性。

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