请求提供Moodle活动完成状态查询的SQL代码（新手咨询）

作为Moodle新手，直接查询mdl_course_modules_completion表只能得到原始状态值，关联其他核心表才能获取更有意义的用户、课程和活动信息。以下是实用的SQL查询示例：

基础全量查询 #

这个查询会返回所有用户在所有课程中的活动完成状态，包含易读的状态描述：

SELECT
    u.id AS user_id,
    u.username,
    u.email,
    c.fullname AS course_name,
    cm.name AS activity_name,
    cmc.completionstate,
    CASE cmc.completionstate
        WHEN 0 THEN '未完成'
        WHEN 1 THEN '已完成'
        WHEN 2 THEN '通过'
        WHEN 3 THEN '未通过'
    END AS completion_status,
    FROM_UNIXTIME(cmc.timemodified) AS completion_updated_datetime
FROM
    mdl_course_modules_completion cmc
JOIN
    mdl_user u ON cmc.userid = u.id
JOIN
    mdl_course_modules cm ON cmc.coursemoduleid = cm.id
JOIN
    mdl_course c ON cm.course = c.id
ORDER BY
    c.fullname, u.username, cm.name;

按场景过滤的查询示例 #

查询特定课程的活动完成状态 #

替换XXX为目标课程ID：

SELECT
    u.id AS user_id,
    u.username,
    cm.name AS activity_name,
    CASE cmc.completionstate
        WHEN 0 THEN '未完成'
        WHEN 1 THEN '已完成'
        WHEN 2 THEN '通过'
        WHEN 3 THEN '未通过'
    END AS completion_status
FROM
    mdl_course_modules_completion cmc
JOIN
    mdl_user u ON cmc.userid = u.id
JOIN
    mdl_course_modules cm ON cmc.coursemoduleid = cm.id
WHERE
    cm.course = XXX
ORDER BY
    u.username, cm.name;

查询特定用户的活动完成状态 #

替换XXX为目标用户ID：

SELECT
    c.fullname AS course_name,
    cm.name AS activity_name,
    CASE cmc.completionstate
        WHEN 0 THEN '未完成'
        WHEN 1 THEN '已完成'
        WHEN 2 THEN '通过'
        WHEN 3 THEN '未通过'
    END AS completion_status,
    FROM_UNIXTIME(cmc.timemodified) AS completion_updated_datetime
FROM
    mdl_course_modules_completion cmc
JOIN
    mdl_course_modules cm ON cmc.coursemoduleid = cm.id
JOIN
    mdl_course c ON cm.course = c.id
WHERE
    cmc.userid = XXX
ORDER BY
    c.fullname, cm.name;

新手注意事项 #

• 确认你的Moodle数据库前缀是mdl_，如果用了自定义前缀，需替换所有表名中的前缀
• completionstate的取值为Moodle通用标准，部分特殊活动类型可能有细微差异
• 执行SQL前建议在测试环境验证，或先备份数据库

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