mysql - 将引用表连接到基表以外的表

Question

基本上我有一个查询如下:

SELECT MAIN.response_id AS 'Response Id', 
    CONCAT(CASE 
        WHEN MAIN.months = 'Jan - Mar' THEN 'March'
        WHEN MAIN.months = 'Apr - Jun' THEN 'June'
        WHEN MAIN.months = 'Jul - Sep' THEN 'September'
        WHEN MAIN.months = 'Oct - Dec' THEN 'December'
        ELSE 'Error'
    END, ' ', MAIN.Year) AS Period,
    CNT.country_name AS Country, INITM.num_modules AS 'Initial Training - Number of Modules', INITTRAINED.num_instr AS 'Initial Training - Instructors Trained', INITPASS.pass_num AS 'Initial Training - Instructors Passed', INITPASS.pass_num / INITTRAINED.num_instr AS 'Initial Training - Pass Percentage'
FROM responses_main AS MAIN -- Main responses table
LEFT OUTER JOIN responses_init_training_modules AS INITM USING (response_id) -- Main INIT training table
LEFT OUTER JOIN responses_init_training_pass_num AS INITPASS USING (response_id) -- Main INIT training table
LEFT OUTER JOIN responses_init_training_instr_trained AS INITTRAINED USING (response_id) -- Main INIT training table
LEFT OUTER JOIN country AS CNT ON MAIN.country_id = CNT.country_id -- Country table
GROUP BY MAIN.response_id, MAIN.months + ' ' + MAIN.Year, CNT.country_name

此查询缺少的是与节目表的联接。每个引用的表(INITM、INITPASS 和 INITTRAINED)都有一个引用程序表的 program_id，但基表 (MAIN) 与程序表无关。当前查询结果表仅返回第一个program_id + 的数据，不显示任何节目信息。

如何导出显示 MAIN.response_id、PROG.program_id、INITM.num_modules、INITPASS.pass_num 和 INITTRAINED.instr_trained 的数据集？我不知道如何使用 3 个引用表中的数据来获取引用两个维度(MAIN 和 PROG)的表。

我在这里列出了我想要实现的目标:

非常感谢任何帮助。

Answer 1

看起来您的数据结构造成了困难。我假设您的 3 个 responses_init_* 是 responses_main 的子类。

假设一个响应仅适用于一个程序，我可能会通过将所有 program_id 列移至 responses_main 表并从那里开始来解决此问题:

    SELECT ...
      FROM program p
      JOIN responses_main rm
        ON rm.program_id = p.program_id 
 LEFT JOIN responses_init_training_modules ritm
        ON ritm.response_id = rm.response_id 
 LEFT JOIN responses_init_training_pass_num ritpn
        ON ritpn.response_id = rm.response_id 
 LEFT JOIN responses_init_training_instr_trained ritit
        ON ritit.response_id = rm.response_id
       ...

注释:

• 我会决定表名称是单数还是复数(responses、program)
• 如果您使用的是季度，请将其保存为 TINYINT 或将范围的开始和结束时间存储为月份或 DATE 的 TINYINT分别地。将任何类型的范围存储为连字符分隔的字符串都会在以后给您带来问题。

更新

与您的 DBA 交谈，解释您的需求，看看他是否会为您进行更改，这很有意义。

同时，可以分别运行三个连接来编程:

LEFT JOIN program ritmp
       ON ritmp.program_id = ritm.program_id
LEFT JOIN program ritpnp
       ON ritpnp.program_id = ritpn.program_id
LEFT JOIN program rititp
       ON rititp.program_id = ritit.program_id

或者你可以使用:

(LEFT) JOIN program p ON p.program_id = COALESCE(ritm.program_id, ritpn.program_id, ritit.program_id)

但是速度会很慢，我建议将其作为最后的手段。