我有一个简单的应用程序，向用户提供多项选择题，并允许他们回答这些问题。这是我的table：
mysql> describe users;
+-------------------------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------------------+---------------------+------+-----+---------+----------------+
| user_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| user_status_id | bigint(20) unsigned | NO | MUL | NULL | |
| profile_id | bigint(20) unsigned | YES | MUL | NULL | |
+-------------------------------+---------------------+------+-----+---------+----------------+
mysql> describe multiple_choice_questions;
+----------------------------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------------+---------------------+------+-----+---------+----------------+
| multiple_choice_question_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| multiple_choice_question_text | varchar(500) | NO | | NULL | |
+----------------------------------+---------------------+------+-----+---------+----------------+
mysql> describe multiple_choice_options;
+------------------------------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------------------------+---------------------+------+-----+---------+----------------+
| multiple_choice_option_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| multiple_choice_option_name | varchar(250) | NO | UNI | NULL | |
| multiple_choice_option_label | varchar(250) | NO | UNI | NULL | |
| multiple_choice_option_description | varchar(500) | NO | | NULL | |
+------------------------------------+---------------------+------+-----+---------+----------------+
mysql> describe questions_x_mc_options;
+------------------------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------------------+---------------------+------+-----+---------+----------------+
| questions_x_mc_option_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| multiple_choice_question_id | bigint(20) unsigned | NO | MUL | NULL | |
| multiple_choice_option_id | bigint(20) unsigned | NO | MUL | NULL | |
+------------------------------+---------------------+------+-----+---------+----------------+
mysql> describe multiple_choice_responses;
+---------------------------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------------+---------------------+------+-----+---------+----------------+
| multiple_choice_response_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| user_id | bigint(20) unsigned | NO | MUL | NULL | |
| multiple_choice_question_id | bigint(20) unsigned | NO | MUL | NULL | |
| multiple_choice_option_id | bigint(20) unsigned | NO | MUL | NULL | |
+---------------------------------+---------------------+------+-----+---------+----------------+我想设计一个查询 questions 那是一个特殊的 user_id 还没有被问到。我最好的尝试是：
SELECT *
FROM multiple_choice_responses
WHERE multiple_choice_question_id NOT IN (
SELECT multiple_choice_question_id
FROM multiple_choice_responses
WHERE user_id = 1
);但这总是返回一个空集。我只想要一个 SELECT 查询，它告诉我特定用户尚未回答的问题。有什么想法吗？
1条答案
按热度按时间thtygnil1#
您不能查询
multiple_choice_responses如果您想要一个未被询问的问题列表，请使用表。该表包含用户和所问问题之间的链接。而是查询
multiple_choice_questions表，并筛选出所有已提出的问题。