Moodle数据库多表条件连接报错求助：CASE WHEN用法问题

嘿，我看到你在写Moodle相关的多表连接查询时遇到了问题，咱们来一步步拆解并解决它～

首先，你的原SQL有两个核心问题导致报错：

1. 无法用CASE WHEN动态指定JOIN的表：SQL解析器在执行查询前就需要确定所有要连接的表，而CASE WHEN是运行时才会计算的逻辑，所以这种写法根本行不通。
2. mdl_modules的连接条件错误：你用completion.coursemoduleid = m.id关联模块类型表，这明显不对——mdl_modules里的id是模块类型的ID（比如assign、quiz对应的ID），而mdl_course_modules的module字段才是关联mdl_modules.id的正确字段，completion.coursemoduleid关联的是mdl_course_modules.id。

下面给你两种可行的解决方案：

方案一：LEFT JOIN所有可能的活动表，用CASE WHEN取值 #

这种方法适合模块类型不多的场景，逻辑清晰易维护：

SELECT 
    completion.userid,
    completion.coursemoduleid,
    completion.timemodified,
    module.course,
    user.idnumber as student_id,
    m.name as module_name,
    -- 根据模块类型选择对应的活动名称
    CASE 
        WHEN m.name = 'assign' THEN assign.name
        WHEN m.name = 'assignment' THEN assignment.name
        WHEN m.name = 'quiz' THEN quiz.name
        ELSE NULL
    END as activity_name
FROM `mdl_course_modules_completion` as completion
JOIN mdl_course_modules as module ON completion.coursemoduleid = module.id
JOIN mdl_user as user ON user.id = completion.userid
JOIN mdl_modules as m ON module.module = m.id  -- 修正后的关联条件
LEFT JOIN mdl_assign as assign ON module.instance = assign.id AND m.name = 'assign'
LEFT JOIN mdl_assignment as assignment ON module.instance = assignment.id AND m.name = 'assignment'
LEFT JOIN mdl_quiz as quiz ON module.instance = quiz.id AND m.name = 'quiz'
LIMIT 0, 30;

思路是：先把所有可能的活动表（assign、assignment、quiz）都用LEFT JOIN连上去，并且通过模块名称过滤，确保只有匹配的表才会返回数据，最后用CASE WHEN从对应的表中取出活动名称。

方案二：用UNION ALL分模块类型查询 #

如果你的数据量比较大，这种方法性能会更优，因为每个分支只连接需要的表：

-- 处理assign类型模块
SELECT 
    completion.userid,
    completion.coursemoduleid,
    completion.timemodified,
    module.course,
    user.idnumber as student_id,
    m.name as module_name,
    assign.name as activity_name
FROM `mdl_course_modules_completion` as completion
JOIN mdl_course_modules as module ON completion.coursemoduleid = module.id
JOIN mdl_user as user ON user.id = completion.userid
JOIN mdl_modules as m ON module.module = m.id
JOIN mdl_assign as assign ON module.instance = assign.id
WHERE m.name = 'assign'

UNION ALL

-- 处理assignment类型模块
SELECT 
    completion.userid,
    completion.coursemoduleid,
    completion.timemodified,
    module.course,
    user.idnumber as student_id,
    m.name as module_name,
    assignment.name as activity_name
FROM `mdl_course_modules_completion` as completion
JOIN mdl_course_modules as module ON completion.coursemoduleid = module.id
JOIN mdl_user as user ON user.id = completion.userid
JOIN mdl_modules as m ON module.module = m.id
JOIN mdl_assignment as assignment ON module.instance = assignment.id
WHERE m.name = 'assignment'

UNION ALL

-- 处理quiz类型模块
SELECT 
    completion.userid,
    completion.coursemoduleid,
    completion.timemodified,
    module.course,
    user.idnumber as student_id,
    m.name as module_name,
    quiz.name as activity_name
FROM `mdl_course_modules_completion` as completion
JOIN mdl_course_modules as module ON completion.coursemoduleid = module.id
JOIN mdl_user as user ON user.id = completion.userid
JOIN mdl_modules as m ON module.module = m.id
JOIN mdl_quiz as quiz ON module.instance = quiz.id
WHERE m.name = 'quiz'

LIMIT 0, 30;

思路是：把每个模块类型的查询单独写出来，用UNION ALL合并结果，这样每个分支只处理对应类型的数据，避免了不必要的表连接。

内容的提问来源于stack exchange，提问作者Md Aman Ullah