MySQL - 在连接时引用伪表内的外部表

Question

请原谅这个问题的措辞，如果它有点偏离。

我有一个可怕的 SQL 查询，下面是一个简化的摘录。

这是一个视频教学网站。查询提取类(class)“实例”(当用户开始类(class)时，它会创建该类(class)的一个实例，并与它们相关联)，并用它来显示每个视频的问题数量的逗号分隔列表(类(class)是由一系列附带问题的视频组成。)

例如，如果一门类(class)有 6 个视频，前 3 个视频有 1 个问题，后 3 个视频每个有 3 个问题，输出将是:

------------------
| num_qs_per_vid |
------------------
| 1,1,1,3,3,3    |
------------------

查询摘录:

SELECT
    ci.instance_id,
    ci.course_id,
    GROUP_CONCAT(COALESCE(questions.num_qs, 0) ORDER BY videos.index) AS num_qs_per_vid,
FROM course_instances ci
LEFT JOIN videos ON ci.course_id IN (
    SELECT course_id_id
    FROM _taxonomy_assocs
    WHERE item_id = videos.id
)
LEFT JOIN (
    SELECT questions.id, questions.vid_id, unit, COUNT(*) as num_qs
    FROM questions
    WHERE unit = **problem** /* <-- need to reference outer ci.course_id */
    GROUP BY questions.vid_id
) questions ON questions.vid_id = videos.id && questions.unit = ci.course_id
WHERE ci.user = '{hard-coded user ID goes here}'
GROUP BY ci.id

我放置 *problem* 的地方我需要引用外部类(class)实例 ID。所以理想情况下我会这样做:

WHERE unit = ci.course_id

但这会引发一个错误，指出 ci.course_id 是未知列。我认为这是时间顺序的问题，即内部部分在外部部分之前运行，但我不知道如何解决这个问题。

如果我指定一个特定类(class)实例 ID，一切都很好。

WHERE unit = "abcdef123"

...正是我想获取多个类(class)实例的时候。

(请注意，任何拼写错误/命名问题都是我为此问题简化查询的结果；它们不在实际查询中。)

Answer 1

LEFT JOIN (
    SELECT questions.id, questions.vid_id, unit, COUNT(*) as num_qs
    FROM questions
    -- REMOVE THIS :
    -- WHERE unit = **problem** /* <-- need to reference outer ci.course_id */
    --               ADD THIS:
    GROUP BY questions.vid_id, questions.unit
) questions ON questions.vid_id = videos.id && questions.unit = ci.course_id

此外，在 SELECT 中包含 questions.id 是个坏主意，因为它不在您的 GROUP BY 中？