mysql - 在 SQL 中返回记录 "only first,"和 "all but first"

Question

我正在尝试返回第一个创建的记录，以及所有但第一个创建的记录，对于给定特定约束的所有个人用户。我的 SQL 坏了，但我终究无法弄清楚哪里/为什么。

对象模型

我有 4 个感兴趣的模型:User、Course、Question 和 QuestionRecord。

每次用户回答问题时，都会生成一个问题记录。 QuestionRecord 是一个包含 User、Course 和 Question 的多对一关系。 (这个基数正确吗？每个问题记录都有一个外键，指向一个用户、一个类(class)和一个问题)

在 ruby 中:

class QuestionRecord < ActiveRecord::Base

  belongs_to :user
  belongs_to :course
  belongs_to :question
…
end

期望的结果 #1:第一条记录

我想要的是为每个 用户返回第一个 问题记录，给定一个类(class)和一个问题。换句话说，如果 Jane 和 Bill 已经回答了这个问题两次，John 没有尝试过，而 Pete 已经回答了 7 次，我想要返回 3 条记录，第一个回答尝试，按创建日期，来自 Jane，Bill，和皮特。

我可以在代码中执行此操作，但为了提高效率，我希望在 SQL 中执行此操作。

这是我的:

SELECT qr.id 
FROM users u 
INNER JOIN question_records qr 
ON u.id = 
  (SELECT x.user_id 
   FROM question_records x 
   WHERE x.course_id = #{course.id} 
   AND x.question_id = #{question.id} 
   AND x.user_id = u.id 
   ORDER BY created_at ASC 
   LIMIT 1 )

这个查询只运行了 40 分钟左右，实际上并没有返回任何东西。我用 50 条记录的子集运行它，并遇到一个奇怪的问题，即所有返回的 qr.id 结果都是“1”

期望的结果 #2:所有但第一条记录

现在，我想要所有用户的所有记录除了第一条记录。我的想法是，本质上，这是同一个查询，只是我想要所有记录，并且我想将返回的索引偏移一个:

SELECT qr.id 
FROM users u 
INNER JOIN question_records qr 
ON u.id = 
  (SELECT x.user_id 
   FROM question_records x 
   WHERE x.course_id = #{course.id} 
   AND x.question_id = #{question.id} 
   AND x.user_id = u.id 
   ORDER BY created_at ASC 
   OFFSET 1 )

但是，很明显，如果第一个查询不起作用，那么第二个查询也不会。

结尾

有人对此查询有任何指导吗？让它尽可能高效会很好，所以我可以在 [course_id, question_id] 或任何需要的地方索引 question_records。我还假设我错过了使用“GROUP BY x.user_id”的可能性，但不确定如何在不添加更多口香糖的情况下添加它。

郑重声明，我们使用的数据库是 MySQL。

Answer 1

对于 #1，如果您的 ID 值是按顺序发布的:

SELECT     MIN(qr.id) AS FirstQRID
FROM         users AS u INNER JOIN
                      question_records AS qr ON qr.user_id = u.id
WHERE     (qr.course_id = #{course.id}) AND (qr.question_id = #{questionid})
GROUP BY u.id

然后，对于#2:

SELECT qr.id
FROM         questions AS qr2
WHERE      (qr2.course_id = #{course.id}) AND (qr2.question_id = #{questionid}) AND
           (NOT qr.id = (SELECT     MIN(qr.id) AS FirstQRID
                         FROM         users AS u INNER JOIN
                                               question_records AS qr ON qr.user_id = u.id
                         WHERE     (qr.course_id = qr2.course_id) AND (qr.question_id = qr2.quesion_id) AND
                                   (qr.user_id = qr2.user_id)))