如何统计Moodle中scorm_scoes_track表内SlideX_QuestionY类问题的value结果数？

开发者阿泽

2026-7-17

问题描述

1名用户完成了2次测试尝试。测试题目为随机抽取，因此不同题目存储在element和value列中。（附scorm_scoes_track表截图）

如何统计scorm_scoes_track表中，所有格式为SlideX_QuestionY的问题对应的value列结果数量？期望结果参考对应截图。

现有查询SQL：

SELECT
    st.* 
    FROM prefix_scorm_scoes_track    AS st
       JOIN prefix_user              AS u   ON st.userid  = u.id
       JOIN prefix_scorm             AS sc  ON sc.id      = st.scormid
       JOIN prefix_course            AS c   ON c.id       = sc.course
    WHERE   sc.course = 684 and st.scormid = 1195  and st.userid = 9780

解决方案

按问题+结果统计数量

如果需要统计每个问题下不同value结果的出现次数（以MySQL为例）：

SELECT
    st.element AS question,
    st.value AS answer_result,
    COUNT(*) AS result_count
FROM prefix_scorm_scoes_track AS st
JOIN prefix_user AS u ON st.userid = u.id
JOIN prefix_scorm AS sc ON sc.id = st.scormid
JOIN prefix_course AS c ON c.id = sc.course
WHERE 
    sc.course = 684 
    AND st.scormid = 1195  
    AND st.userid = 9780
    AND st.element REGEXP '^Slide[0-9]+_Question[0-9]+$'
GROUP BY st.element, st.value
ORDER BY st.element, st.value;

按问题统计总尝试数

如果只需要统计每个SlideX_QuestionY格式问题的总提交次数：

SELECT
    st.element AS question,
    COUNT(*) AS total_attempts
FROM prefix_scorm_scoes_track AS st
JOIN prefix_user AS u ON st.userid = u.id
JOIN prefix_scorm AS sc ON sc.id = st.scormid
JOIN prefix_course AS c ON c.id = sc.course
WHERE 
    sc.course = 684 
    AND st.scormid = 1195  
    AND st.userid = 9780
    AND st.element REGEXP '^Slide[0-9]+_Question[0-9]+$'
GROUP BY st.element
ORDER BY st.element;