mysql - 尝试进行查询以获得测试中所有问题的所有答案

Question

MySQL从来都不是我的强项。我创建了一个在线应用程序，允许用户创建测试供其他人参加，只是为了看看他们是否通过或失败。这些测试的数据库结构如下:

user_test
  - id (integer, primary key, auto increment)
  - owner (integer)

user_test_question
 - id (integer, primary key, auto increment)
 - belongs_to_test (integer)
 - question_text (varchar)

user_test_answer
 - id (integer, primary key, auto increment)
 - belongs_to_question (integer)
 - answer_text (integer)
 - answer_correct (integer)

这是我为测试场景创建的架构

CREATE TABLE users (
  user_id INT(11) PRIMARY KEY AUTO_INCREMENT
);

CREATE TABLE user_tests (
  test_id INT(11) PRIMARY KEY AUTO_INCREMENT,
  test_owner INT(11) NOT NULL
);

CREATE TABLE test_questions (
  question_id INT(11) PRIMARY KEY AUTO_INCREMENT,
  question_belongs_to INT(11) NOT NULL,
  question_text VARCHAR(200) NOT NULL
);

CREATE TABLE test_answers (
  answer_id INT(11) PRIMARY KEY AUTO_INCREMENT,
  answer_belongs_to INT(11) NOT NULL,
  answer_text VARCHAR(200) NOT NULL,
  answer_correct SMALLINT(1) DEFAULT 0
);

INSERT INTO users (user_id) VALUES (NULL);

INSERT INTO user_tests(test_id, test_owner) VALUES (NULL, 1);
INSERT INTO test_questions(question_id, question_belongs_to, question_text) VALUES (NULL, 1, "first question");
INSERT INTO test_questions(question_id, question_belongs_to, question_text) VALUES (NULL, 1, "second question");
INSERT INTO test_questions(question_id, question_belongs_to, question_text) VALUES (NULL, 1, "third question");

INSERT INTO test_answers(answer_id, answer_belongs_to, answer_text, answer_correct) VALUES (null, 1, "Question 1 - answer 1", 0);
INSERT INTO test_answers(answer_id, answer_belongs_to, answer_text, answer_correct) VALUES (null, 1, "Question 1 - answer 2", 0);
INSERT INTO test_answers(answer_id, answer_belongs_to, answer_text, answer_correct) VALUES (null, 1, "Question 1 - answer 3", 0);

INSERT INTO test_answers(answer_id, answer_belongs_to, answer_text, answer_correct) VALUES (null, 2, "Question 2 - answer 1", 0);
INSERT INTO test_answers(answer_id, answer_belongs_to, answer_text, answer_correct) VALUES (null, 2, "Question 2 - answer 1", 0);

INSERT INTO test_answers(answer_id, answer_belongs_to, answer_text, answer_correct) VALUES (null, 3, "Question 3 - answer 1", 0);
INSERT INTO test_answers(answer_id, answer_belongs_to, answer_text, answer_correct) VALUES (null, 3, "Question 3 - answer 2", 0);
INSERT INTO test_answers(answer_id, answer_belongs_to, answer_text, answer_correct) VALUES (null, 3, "Question 3 - answer 3", 0);
INSERT INTO test_answers(answer_id, answer_belongs_to, answer_text, answer_correct) VALUES (null, 3, "Question 3 - answer 4", 0);

这是我尝试使用的查询:

SELECT * FROM `user_tests`, `test_questions`, `test_answers`
WHERE `user_tests`.`test_owner` = 1 #The "user id" 
AND `test_answers`.`answer_id` = `test_questions`.`question_id`
GROUP BY `test_questions`.`question_id` 
ORDER BY `test_questions`.`question_id`

生成的结果仅显示每个问题的一个答案，预期结果如下所示:

答案按问题 ID 的顺序列出，如下所示:

question_id: 1, answer_id: 1, text: Question 1 - Answer 1
question_id: 1, answer_id: 2, text: Question 1 - Answer 2
question_id: 1, answer_id: 3, text: Question 1 - Answer 3
question_id: 2, answer_id: 4, text: Question 2 - Answer 1
question_id: 2, answer_id: 5, text: Question 2 - Answer 2
question_id: 3, answer_id: 6, text: Question 3 - Answer 1
question_id: 3, answer_id: 7, text: Question 3 - Answer 2
question_id: 3, answer_id: 8, text: Question 3 - Answer 3
question_id: 3, answer_id: 9, text: Question 3 - Answer 4

Answer 1

您正在对 Question_id 使用GROUP BY。这将使每个问题仅显示一行，您必须在查询中删除这一行

查询中还有以下行:

AND `test_answers`.`answer_id` = `test_questions`.`question_id`

这对我来说似乎不正确？这应该是属于问题？|

我在自己的数据库中重建表(sqlfiddle 遇到超时):

SELECT * FROM `user_tests`, `test_questions`, `test_answers`
WHERE `user_tests`.`test_owner` = 1 #The "user id" 
AND `test_answers`.`answer_belongs_to` = `test_questions`.`question_id`
ORDER BY `test_questions`.`question_id`, `test_answers`.`answer_id`

answer_id = 5 的answer_text 不正确，但我猜这只是示例数据中的拼写错误。