sql - 如何使这个子子查询起作用？

Question

我正在尝试在一个查询中执行此操作。几天前我问过类似的问题，但我的个人要求发生了变化。

我有一个游戏类型的网站，用户可以在其中参加“类(class)”。我的数据库中有三个表。

我正在使用MySQL。我有四张 table :

• hl_classes(int id，int 教授，varchar 类，文本描述)
• hl_classes_lessons(int id，int类 ID，varchar类(class)标题，varchar lexiconLink，文本类(class)数据)
• hl_classes_answers(int id, int Lesson_id, int 学生,文本submit_answer，整数百分比)

hl_classes 存储网站上的所有类。

这些类(class)是每个类(class)的单独类(class)。一个类(class)可以有无限的类(class)。每节课都有特定的学期。

hl_classes_terms 存储所有术语的列表，当前术语的字段处于事件状态 = '1'。

当用户提交类(class)答案时，答案将存储在 hl_classes_answers 中。用户每节课只能回答一次。类(class)必须按顺序回答。所有用户都参加所有“类(class)”。

我想做的是为每个用户在每节课中捕获下一课。当用户开始时，他们处于第 1 学期。当他们完成每门类(class)的全部 10 节课时，他们将进入第 2 学期。当他们完成每门类(class)的第 20 课时，他们将进入第 3 学期。假设我们知道用户所在的学期通过 PHP 变量 $term 来实现。

所以这是我目前正在尝试解决的查询，但它不起作用。具体是因为 WHERE 子句中的 hC.id 未知

SELECT hC.id, hC.class, (SELECT MIN(output.id) as nextLessonID
  FROM ( SELECT id, class_id
   FROM hl_classes_lessons hL
   WHERE hL.class_id = hC.id
   ORDER BY hL.id
   LIMIT $term,10 ) as output
  WHERE output.id NOT IN (SELECT lesson_id FROM hl_classes_answers WHERE student = $USER_ID)) as nextLessonID
FROM hl_classes hC

我这个查询背后的逻辑是首先对于每个类；选择当前用户所在学期的所有类(class)。从中整理出用户已经完成的类(class)，并获取尚未完成的类(class)的最小 ID。这将是用户必须做的类(class)。

我希望我的问题已经说得足够清楚了。

Answer 1

我的假设是，您想要一个查询来告诉特定学生在给定学期的下一课是什么，或者如果该学生在该学期没有其他类(class)，则返回 null。结果应该是一行或为空。

为了以任何效率(恕我直言，理智)做到这一点，您需要首先重新审视您的表结构和关于数据的假设。我根据您提供的表格结构以及您如何描述类(class)编号假设，例如，类(class) 1，类(class) 1, 2, 3, ..., 10, 11, ... 20, 21 , ..., 30，然后是第 2 课，第 1...30 课，然后是第 3 课，第 1...30 课，等等。此外，每个类(class)的第 1-10 课对应于第 1、11-20 学期，到第 2 学期，21-30 到第 3 学期。最后，学期按顺序完成 - 第 3 课第 10 课在第 1 课第 11 课之前完成。

首先，我建议使用唯一的 id 字段(可能是自动增量)和单独的 class_num，而不是使用您的类号作为唯一标识符和订购号(类 1 发生在类 2 之前，等等)订购号字段。 (这对于类(class)表来说不如对于类(class)表那么重要，如下所述。)

接下来，类似地，类(class)应该获得一个与其类(class)编号字段分开的唯一 ID 字段。 id就是PK。这个唯一的 ID 对于大大简化您想要的查询以及您可能需要的任何其他查询是必要的。如果没有它，您将处理两个字段的复合键，这会使许多连接和子查询变得异常复杂。您可能需要在 class_id 和 Lesson_num 上添加一个额外的唯一索引，以便类(class)编号不会在类(class)中重复使用。此外，此表应包含分配给特定类(class)的特定类(class)的 term_num(或 term_id)。这将使您不必使用过于复杂的 MOD 公式来计算类(class)的术语。那就太过分了。只需将学期编号与类(class)信息一起存储，您就可以根据需要组织学期。

接下来，answers 表的 id 字段应该是唯一的自动增量。如果它很重要，您可能还需要在lesson_id和student_id上有一个唯一索引(尽管这意味着要么不重考，要么重考覆盖)。

所以我现在有:

• hl_classes(int id，int class_num，教授，class_name，描述)PK:id，autoinc
• hl_classes_lessons (int id, int class_id, int Lesson_num, int term_num, l_title, l_link, l_data) PK: id, autoinc;唯一键:class_id、lesson_num
• hl_classes_answers (int id, int Lesson_id, int Student, ans, pct) PK: id, autoinc;唯一键:lesson_id，学生

有了这个，我想出了:

select hC.id as next_class_id, hL.id as next_lesson_id, hC.class as next_class, hL.term_num as term_num, hC.class_num as next_class_num, hL.lesson_num AS next_lesson_num
from hl_classes hC
left join hl_classes_lessons hL on hL.class_id = hC.id
where hL.term_num = $TERM_NUM
and hL.id not in (
    select hA.lesson_id
    from hl_classes_answers hA
    where student = $USER_ID
)
order by hC.class_num, hL.lesson_num
limit 1;

这将为您返回包含该学生下一个类(给定该学期)的相关信息的一行，或者全部为空。请注意，ID 不用于显示，因为它们可以是任何数字。您将显示 _num 字段。