如何使这个子查询工作？

Question

我试图在一个查询中完成这个任务。几天前，我问了一个类似的问题，但我的个人要求已经改变了。

我有一个游戏类型的网站，用户可以参加“课程”。我的数据库中有三个表。

我正在使用MySQL。我有四张桌子：

(int id，int教授，varchar类，text description)

• hl_classes_lessons (int id，int class_id，varchar lessonTitle，varchar lexiconLink，text lessonData)

• hl_classes_answers (int id，int lesson_id，int学生，text submit_answer，int %)

)

hl_classes将所有的类存储在网站上。

这些课程是每节课的个别课程。一门课可以有无穷的课程。每一堂课都有一个特定的术语。

hl_classes_terms存储所有术语的列表，而当前项具有active = '1‘字段。

当用户向一个课程提交他们的答案时，它被存储在hl_classes_answers中。用户只能回答每堂课一次。必须按顺序回答经验教训。所有用户都参加所有的“课程”。

我想要做的是抓住下一课，让每个用户在每堂课上做。当用户开始学习第一学期的时候，当他们完成每堂课的全部10课时，他们就会转到第2学期。当他们完成第20课时，他们就会转到第3学期。假设我们知道用户是通过$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

这个查询背后的逻辑首先是针对每个类；选择当前用户所在术语中的所有课程。在此基础上，整理出用户已经完成的课程，并获取尚未完成的课程的最小id。这将是用户必须做的一课。

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

Answer 1

我的假设是，您需要一个查询，该查询将告诉特定学生在给定学期的下一课是什么，如果在该学期中没有更多的课程，则为null。结果应该是一行或null。

为了以任何效率(和IMHO，理智)来实现这一点，您需要首先重新检查您的表结构和有关数据的假设。我假设从你所提供的表格结构以及你如何描述课程编号，例如，第1课、第1课、第2课、第3课、.、第10课、第11课、. 20、21、. 30，然后第2类，第1 .30课，然后第3类，第1.30课，等等。此外，每一班的第1至10课相当于第1、11-20、2至2学期，以及21-30至3学期。第三课第10课是在第11课之前完成的。

首先，我建议使用唯一的id字段(可能是自动递增的)和单独的class_num字段，而不是同时使用类号作为唯一标识符和排序号(类1发生在类2之前等)。(对于类表来说，这一点比下面描述的“课程”表的重要性要小。)

接下来，并且类似地，课程应该得到一个独立于它的“课号”字段的唯一id字段。身份是PK。为了大大简化所需的查询，以及可能需要的任何其他查询，这个唯一的id是必需的。没有它，您将处理一个双字段复合键，它会使许多联接和子查询变得可怕地复杂。您可能需要在class_id和lesson_num上添加一个额外的唯一索引，这样就不会在类中重用一个课号。另外，这个表应该包含为特定类分配特定课程的term_num (或term_id)。这将使您不必计算在使用过于复杂的MOD公式中的教训是什么。那就太过分了。只需将学期编号与课程信息一起存储，您就可以任意组织术语。

接下来，答案表的id字段应该是唯一的自动增量。如果这很重要，您可能还希望在lesson_id和student_id上建立一个唯一的索引(尽管这意味着不需要重新编写，或者是重取覆盖)。

所以我现在有：

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，lesson_num

• hl_classes_answers (int id，int lesson_id，int class_num，ans，pct) PK: id，autoinc；

就这样，我想出了：

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;

这将给您返回一行，包含有关该学生的下一堂课的相关信息，或者所有的空值。请注意，这些ids不是用来显示的，因为它们可能是任何ol‘号。您将显示_num字段。

Answer 2

我不知道您希望最终结果是什么样子，以及为什么在查询中有限制的$term，但是如果您想获得用户的所有类和下一课(如果可用的话)，您可以使用以下内容：

SELECT c.*, l.*
FROM hl_classes c JOIN (
        SELECT l.class_id, MIN(l.id) NextLessonID
        FROM hl_classes_lessons l LEFT JOIN (
                SELECT sca.class_id, MAX(sca.lesson_id) MaxID
                FROM hl_classes_answers sca
                WHERE sca.student = $USER_ID
                GROUP BY sca.class_id
            ) cm ON (l.class_id = cm.class_id AND l.id > cm.MaxID) OR cm.class_id IS NULL
        GROUP BY l.class_id
    ) nid ON c.id = nid.class_id
    JOIN hl_classes_lessons l ON c.id = l.class_id AND l.id = nid.NextLessonID