SQL 查询帮助 - 多项选择测试评分

Question

假设我有一个 Student 表，它有一个 int ID。 我有一组固定的 10 个多项选择题，有 5 个可能的答案。 我有一个标准化答案表，其中包含问题 id、Student.answer (1-5) 和 Student.ID

我正在尝试编写一个查询，该查询将返回超过特定百分比的所有分数。 为此，我编写了一个简单的 UDF，它接受 Student.answers 和正确答案，因此它有 20 个参数。

我开始想知道是否最好对答案表进行非规范化，将其带入我的应用程序并让我的应用程序进行评分。

有人曾经处理过这样的事情并且有洞察力吗？

Answer 1

从长远来看，您正在谈论的架构可能会变得非常麻烦，如果您需要更改问题，则意味着您正在使用的 UDF 需要进行更多更改。

我认为您可能可以在代码中进行分析，而不必对数据库进行反规范化。 非规范化还可能导致不灵活，或者至少增加未来的更新费用。

Answer 2

如果我正确理解你的模式和问题，那么这样的事情怎么样：

select student_name, score
from students
  join (select student_answers.student_id, count(*) as score
        from student_answers, answer_key
        group by student_id
        where student_answers.question_id = answer_key.question_id
          and student_answers.answer = answer_key.answer)
  as student_scores on students.student_id = student_scores.student_id
where score >= 7
order by score, student_name

例如，应该选择分数为 7 或更高的学生。 只需根据您的目的调整 where 子句即可。

Answer 3

我可能会将其留给您的应用程序来执行评分。 查看 Jeff Atwood 的也许标准化并不正常。

Answer 4

这个查询应该很容易......假设您在问题表中存储了正确的答案。 你确实在问题表中存储了正确答案，对吗？

Answer 5

非规范化通常被认为是最后的手段。 这个问题似乎与很常见的调查应用程序非常相似。 如果没有看到你的数据模型，很难提出解决方案，但我会说这绝对是可能的。 我想知道为什么该函数需要 20 个参数？

在大多数情况下，基于关系集的解决方案会更简单、更快。

Answer 6

没办法，你肯定想保持正常化。 这甚至不是那么难的查询。

基本上，您需要将学生的正确答案与该问题的总答案连接起来，然后进行计数。 这将为您提供正确的百分比。 对每个学生都这样做，并将最小正确百分比放入 where 子句中。