我正在尝试为给定某些约束的所有单个用户返回第一个创建的记录,以及除第一个创建的记录之外的所有记录。我的SQL坏了,但我一辈子都不知道在哪里/为什么。
对象模型
我有 4 个感兴趣的模型:用户、课程、问题和问题记录。
每次用户回答问题时,都会生成一个问题记录。问题记录是包含用户、课程和问题的多对一。(这个基数正确吗?每个问题记录都有一个外键,只指向一个用户、一个课程和一个问题)
在 Ruby 中:
class QuestionRecord < ActiveRecord::Base
belongs_to :user
belongs_to :course
belongs_to :question
…
end
期望的结果 #1:第一条记录
我想要的是返回每个用户的第一个问题记录,给定一个课程和一个问题。换句话说,如果简和比尔回答了两次问题,约翰没有尝试过,而皮特回答了7次,我希望从简、比尔和皮特那里返回3条记录,第一次回答尝试,按创建日期。
我可以在代码中执行此操作,但希望在 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:除第一条记录外的所有记录
现在在这里,我想要除第一条记录之外的所有用户的所有记录。我的想法是,从本质上讲,这是相同的查询,除了我想要所有记录,并且我想将返回的索引偏移 1:
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。
对于 #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)))
对于第一个,这应该足够了
SELECT qr.id, max(field_1), max(field_2), etc.
FROM users u
INNER JOIN question_records qr
ON u.id = qr.user_id
GROUP BY qr.id
ORDER BY [the field that determines the first question]
对于第二个问题,您有几个选择。 1)只需做一个选择,然后在应用层中抛出第一个。2) 开发一个返回任意范围的查询
选项 2 被称为每组 top n 问题,另外您将限制两端的范围。