LEFT JOIN不返回空值，也不返回MySQL中预期的记录数
LEFT JOIN不返回空值，也不返回MySQL中预期的记录数
提问于 2017-08-10 01:53:42
我有一个测验应用程序与这个数据库，我被要求打印正确的答案，即考试关键和答案为每个用户，但如果用户留下任何答案为空，它应该是null或'x‘或任何东西来识别它。
每个问题都有几个干扰项(选项)，但只有一个是正确的(distractors.correct = 1)，并且只有一个答案(answers.question_id)，并检查答案answers.answer = distractor.option是否正确。所需输出：
+-----------+---------------+----------+-----------+---------------+-----------+----------+
| exam_id | question_id | option | correct | question_id | user_id | answer |
|-----------+---------------+----------+-----------+---------------+-----------+----------|
| 195 | 41303 | E | 0 | 41303 | 4404 | null |
| 195 | 41483 | A | 0 | 41483 | 4404 | A |
| 195 | 41350 | A | 0 | 41350 | 4404 | A |
| 195 | 41401 | E | 0 | 41401 | 4404 | E |
| 195 | 41271 | A | 0 | 41271 | 4404 | A |
| 195 | 41448 | D | 1 | 41448 | 4404 | D |
| 195 | 41320 | E | 1 | 41320 | 4404 | E |
| 195 | 41499 | C | 1 | 41499 | 4404 | C |
| 195 | 41369 | C | 1 | 41369 | 4404 | null |
| 195 | 41418 | E | 0 | 41418 | 4404 | E |
| 195 | 41287 | D | 1 | 41287 | 4404 | D |
| 195 | 41467 | B | 1 | 41467 | 4404 | B |
| 195 | 41337 | C | 1 | 41337 | 4404 | C |
| 195 | 41385 | E | 0 | 41385 | 4404 | E |客户端结果应为。
key DBCCDBCCABDBBADCBCCDDCCADBACDCBACCCDDBDDADAAACCACADBDCBBDDDADABBACDADCBDABADCCAACCBBBABBACAAADXADCAABBBBCDDADCCBDBACDCABDABDACACAC
0001 DBCCBBCCBBBBBADBXXXXDCBABBACDCBACCCDDBDCADAAABCAADCBDCBBDDDDDCBBACDABCBDDBACACAACCBBBAABACABDDBCDCBABCBBCDDADCCBDBACDCDCDBDBCCACAC
0002 DACCBBCCCBDDBAACBCCDCBBABAABDCBACCCDDBDCACAAACCADABBDCBCADDDDABBBCAADCBDABACCCABBDCDBDCDADABBDDBACBABBDBDDDADCCBDBABBCDBDADBBCACAB
0003 CCCCCBCCBBDCBBDCBCCADCAABAAADCBABCCDCCDCADAAABCADABBCCBBBDBADABBACDADCBAABACBCCCCDCDCAADACABADBCCCBABBDDCDDDDCCBBBACDCDBDDBBACADAC
0004 CCDACBCCBBDDBADBBCCBABBADBACDCBACCCDDCDCADAAACCADADBDCBADDDDDABBACAACCBCACACCCAACCBBBAAAACABBDAACCCABBDBCDDADCCBDBACDCACBADCCCACAC
0005 CCDCCBCCABDDBADCBCCCDCBACBABDCBACCCDDBDCADAAABDDDDDDDABADDDBDABBACDAACBDACADDCCACCBBBACBACADADBADCBABBABCDDADCCBDBACDCADDBDBACADAC
0006 CCCCCBCCABDDCADCBCDCDCAACBACDCBABACDDBDCADAADCCADACCAABABDDBDABBACDABCADABADAACACCDBBABBACADBDCADCAACBDBCDDADCCBDBABDCABDABBBBDCAC我知道这将包含一个我不需要的转置，因为我将在数组/集合中处理php中的结果。第一行有所有正确的答案/选项，接下来的行有每个用户的答案。
我试过了：
select answers.exam_id, distractors.question_id, distractors.option, distractors.correct, answers.question_id, answers.user_id, answers.answer
from distractors
LEFT JOIN answers on distractors.question_id = answers.question_id
AND answers.answer = distractors.option
AND answers.user_id = 4404
WHERE answers.exam_id = 195
ORDER BY answers.user_id
;
+-----------+---------------+----------+-----------+---------------+-----------+----------+
| exam_id | question_id | option | correct | question_id | user_id | answer |
|-----------+---------------+----------+-----------+---------------+-----------+----------|
| 195 | 41263 | C | 1 | 41263 | 4404 | C |
| 195 | 41259 | C | 0 | 41259 | 4404 | C |
| 195 | 41260 | E | 0 | 41260 | 4404 | E |
| 195 | 41261 | B | 0 | 41261 | 4404 | B |
| 195 | 41262 | E | 0 | 41262 | 4404 | E |
| 195 | 41264 | C | 1 | 41264 | 4404 | C |
| 195 | 41265 | E | 1 | 41265 | 4404 | E |
| 195 | 41266 | A | 0 | 41266 | 4404 | A |
| 195 | 41267 | A | 0 | 41267 | 4404 | A |
| 195 | 41268 | A | 1 | 41268 | 4404 | A |
| 195 | 41269 | E | 0 | 41269 | 4404 | E |
| 195 | 41270 | B | 0 | 41270 | 4404 | B |
| 195 | 41271 | A | 0 | 41271 | 4404 | A |
| 195 | 41272 | C | 0 | 41272 | 4404 | C |
| 195 | 41273 | E | 0 | 41273 | 4404 | E |
| 195 | 41274 | A | 0 | 41274 | 4404 | A |
| 195 | 41275 | E | 0 | 41275 | 4404 | E |
| 195 | 41276 | C | 0 | 41276 | 4404 | C |
| 195 | 41277 | D | 1 | 41277 | 4404 | D |
| 195 | 41278 | D | 0 | 41278 | 4404 | D |
| 195 | 41279 | C | 1 | 41279 | 4404 | C |
| 195 | 41280 | B | 1 | 41280 | 4404 | B |
| 195 | 41282 | D | 0 | 41282 | 4404 | D |
| 195 | 41281 | E | 1 | 41281 | 4404 | E |
| 195 | 41283 | D | 0 | 41283 | 4404 | D |
| 195 | 41284 | D | 1 | 41284 | 4404 | D |
| 195 | 41285 | E | 1 | 41285 | 4404 | E |
| 195 | 41286 | C | 1 | 41286 | 4404 | C |
| 195 | 41287 | D | 1 | 41287 | 4404 | D |
| 195 | 41288 | A | 1 | 41288 | 4404 | A |
| 195 | 41290 | C | 1 | 41290 | 4404 | C |
| 195 | 41289 | B | 1 | 41289 | 4404 | B |
| 195 | 41291 | E | 1 | 41291 | 4404 | E |
| 195 | 41292 | B | 1 | 41292 | 4404 | B |
| 195 | 41293 | C | 1 | 41293 | 4404 | C |
| 195 | 41294 | D | 1 | 41294 | 4404 | D |
| 195 | 41296 | A | 0 | 41296 | 4404 | A |
| 195 | 41295 | C | 1 | 41295 | 4404 | C |
| 195 | 41297 | E | 0 | 41297 | 4404 | E |
| 195 | 41298 | C | 0 | 41298 | 4404 | C |
| 195 | 41299 | E | 0 | 41299 | 4404 | E |
| 195 | 41300 | B | 0 | 41300 | 4404 | B |
| 195 | 41301 | A | 1 | 41301 | 4404 | A |
| 195 | 41302 | B | 0 | 41302 | 4404 | B |
| 195 | 41303 | E | 0 | 41303 | 4404 | E |
| 195 | 41511 | A | 0 | 41511 | 4404 | A |
| 195 | 41512 | C | 0 | 41512 | 4404 | C |
| 195 | 41513 | B | 1 | 41513 | 4404 | B |
+-----------+---------------+----------+-----------+---------------+-----------+----------+
245 rows in set没有得到预期的321个问题/答案和伴随的空值。
我尝试了几个连接，但没有一个给出预期的结果。
表：
DESCRIBE answers;
+-------------+------------------+--------+-------+-----------+----------------+
| Field | Type | Null | Key | Default | Extra |
|-------------+------------------+--------+-------+-----------+----------------|
| id | int(10) unsigned | NO | PRI | <null> | auto_increment |
| user_id | int(10) unsigned | NO | MUL | <null> | |
| question_id | int(10) unsigned | NO | MUL | <null> | |
| answer | char(255) | YES | | <null> | |
| created_at | timestamp | YES | | <null> | |
| updated_at | timestamp | YES | | <null> | |
| deleted_at | timestamp | YES | | <null> | |
| exam_id | int(10) unsigned | NO | | <null> | |
+-------------+------------------+--------+-------+-----------+----------------+
8 rows in set
Time: 0.006s
DESCRIBE questions;
+------------+------------------+--------+-------+-----------+----------------+
| Field | Type | Null | Key | Default | Extra |
|------------+------------------+--------+-------+-----------+----------------|
| id | int(10) unsigned | NO | PRI | <null> | auto_increment |
| slot_id | int(10) unsigned | NO | MUL | <null> | |
| order | smallint(6) | NO | | <null> | |
| text | text | NO | | <null> | |
| created_at | timestamp | YES | | <null> | |
| updated_at | timestamp | YES | | <null> | |
+------------+------------------+--------+-------+-----------+----------------+
6 rows in set
Time: 0.008s
DESCRIBE distractors;
+-------------+------------------+--------+-------+-----------+----------------+
| Field | Type | Null | Key | Default | Extra |
|-------------+------------------+--------+-------+-----------+----------------|
| id | int(10) unsigned | NO | PRI | <null> | auto_increment |
| question_id | int(10) unsigned | NO | MUL | <null> | |
| option | varchar(255) | NO | | <null> | |
| distractor | text | NO | | <null> | |
| correct | tinyint(1) | NO | | <null> | |
| created_at | timestamp | YES | | <null> | |
| updated_at | timestamp | YES | | <null> | |
+-------------+------------------+--------+-------+-----------+----------------+
7 rows in set
Time: 0.006s
describe exams;
+---------------+------------------+--------+-------+-----------+----------------+
| Field | Type | Null | Key | Default | Extra |
|---------------+------------------+--------+-------+-----------+----------------|
| id | int(10) unsigned | NO | PRI | <null> | auto_increment |
| applicated_at | datetime | NO | | <null> | |
| board_id | int(10) | NO | | <null> | |
| active | tinyint(1) | NO | | 1 | |
| duration | int(10) unsigned | NO | | <null> | |
| passing_grade | int(10) | YES | | <null> | |
| annotation | text | NO | | <null> | |
| deleted_at | timestamp | YES | | <null> | |
| created_at | timestamp | YES | | <null> | |
| updated_at | timestamp | YES | | <null> | |
+---------------+------------------+--------+-------+-----------+----------------+
10 rows in set
Time: 0.014s示例数据：
select * from distractors limit 1000;
+------+---------------+----------+--------------------------------------------------------------------------------------------------------+-----------+---------------------+---------------------+
| id | question_id | option | distractor | correct | created_at | updated_at |
|------+---------------+----------+--------------------------------------------------------------------------------------------------------+-----------+---------------------+---------------------|
| 1 | 1 | A | Diuresis osmótica | 0 | 2017-01-12 21:20:29 | 2017-01-12 21:20:29 |
| 2 | 1 | B | Diabetes insípida central | 1 | 2017-01-12 21:20:29 | 2017-01-12 21:20:29 |
| 3 | 1 | C | Diabetes insípida nefrogénica | 0 | 2017-01-12 21:20:29 | 2017-01-12 21:20:29 |
| 4 | 1 | D | Administración de soluciones hipertónicas | 0 | 2017-01-12 21:20:29 | 2017-01-12 21:20:29 |
| 5 | 1 | E | Sx de secreción inapropiada de hormona antidiurética | 0 | 2017-01-12 21:20:29 | 2017-01-12 21:20:29 |
| 6 | 2 | A | TC cerebral | 0 | 2017-01-12 21:20:29 | 2017-01-12 21:20:29 |
| 7 | 2 | B | Sodio urinario | 0 | 2017-01-12 21:20:29 | 2017-01-12 21:20:29 |
| 8 | 2 | C | Osmolaridad sérica | 0 | 2017-01-12 21:20:30 | 2017-01-12 21:20:30 |
| 9 | 2 | D | Osmolaridad urinaria | 0 | 2017-01-12 21:20:30 | 2017-01-12 21:20:30 |
| 10 | 2 | E | Respuesta a vasopresina | 1 | 2017-01-12 21:20:30 | 2017-01-12 21:20:30 |
| 11 | 3 | A | Pérdida extrarrenal de agua | 0 | 2017-01-12 21:20:30 | 2017-01-12 21:20:30 |
| 12 | 3 | B | Aumento en la reabsorción de sodio | 0 | 2017-01-12 21:20:30 | 2017-01-12 21:20:30 |
| 13 | 3 | C | Aumento en la depuración de agua libre | 1 | 2017-01-12 21:20:30 | 2017-01-12 21:20:30 |
| 14 | 3 | D | Disminución en la depuración de agua libre | 0 | 2017-01-12 21:20:30 | 2017-01-12 21:20:30 |
| 15 | 3 | E | Resistencia tubular a la acción de la hormona antidiurética | 0 | 2017-01-12 21:20:30 | 2017-01-12 21:20:30 |
| 16 | 4 | A | Solución Hartmann | 0 | 2017-01-12 21:20:30 | 2017-01-12 21:20:30 |
| 17 | 4 | B | Solución salina al 3% | 0 | 2017-01-12 21:20:30 | 2017-01-12 21:20:30 |
| 18 | 4 | C | Solución salina al 0.9% | 0 | 2017-01-12 21:20:30 | 2017-01-12 21:20:30 |
| 19 | 4 | D | Solución glucosada al 5 % | 0 | 2017-01-12 21:20:30 | 2017-01-12 21:20:30 |
| 20 | 4 | E | Solución isotónica con HCO<sub>3</sub> | 1 | 2017-01-12 21:20:30 | 2017-01-12 21:20:30 |
| 21 | 5 | A | Mielinólisis | 0 | 2017-01-12 21:20:30 | 2017-01-12 21:20:30 |
| 22 | 5 | B | Edema cerebral | 1 | 2017-01-12 21:20:30 | 2017-01-12 21:20:30 |
select * from distractors limit 1000
+------+-----------+---------------+----------+---------------------+---------------------+--------------+-----------+
| id | user_id | question_id | answer | created_at | updated_at | deleted_at | exam_id |
|------+-----------+---------------+----------+---------------------+---------------------+--------------+-----------|
| 26 | 2782 | 1 | B | 2017-01-14 15:20:39 | 2017-01-14 15:20:39 | <null> | 1 |
| 27 | 2755 | 1 | B | 2017-01-14 15:20:39 | 2017-01-14 15:20:39 | <null> | 1 |
| 28 | 2741 | 1 | B | 2017-01-14 15:20:42 | 2017-01-14 15:20:42 | <null> | 1 |
| 29 | 2755 | 2 | E | 2017-01-14 15:20:51 | 2017-01-14 15:20:51 | <null> | 1 |
| 30 | 2755 | 3 | C | 2017-01-14 15:21:11 | 2017-01-14 15:21:11 | <null> | 1 |
| 31 | 2755 | 4 | D | 2017-01-14 15:21:25 | 2017-01-14 15:21:25 | <null> | 1 |
| 32 | 2741 | 2 | E | 2017-01-14 15:21:34 | 2017-01-14 15:21:34 | <null> | 1 |
| 33 | 2763 | 1 | B | 2017-01-14 15:21:39 | 2017-01-14 15:21:39 | <null> | 1 |
| 34 | 2782 | 4 | D | 2017-01-14 15:21:40 | 2017-01-14 15:21:40 | <null> | 1 |
| 35 | 2755 | 5 | B | 2017-01-14 15:21:52 | 2017-01-14 15:21:52 | <null> | 1 |
| 36 | 2782 | 5 | B | 2017-01-14 15:22:06 | 2017-01-14 15:22:06 | <null> | 1 |
| 37 | 2763 | 2 | D | 2017-01-14 15:22:07 | 2017-01-14 15:22:07 | <null> | 1 |
| 38 | 2775 | 116 | E | 2017-01-14 15:22:19 | 2017-01-14 15:22:19 | <null> | 1 |
| 39 | 2775 | 117 | A | 2017-01-14 15:22:23 | 2017-01-14 16:59:31 | <null> | 1 |
| 40 | 2801 | 21 | B | 2017-01-14 15:22:25 | 2017-01-14 15:22:25 | <null> | 1 |
| 41 | 2775 | 118 | A | 2017-01-14 15:22:29 | 2017-01-14 15:22:29 | <null> | 1 |
| 42 | 2868 | 301 | C | 2017-01-14 15:22:30 | 2017-01-14 15:22:47 | <null> | 2 |
| 43 | 2763 | 3 | C | 2017-01-14 15:22:36 | 2017-01-14 15:22:36 | <null> | 1 |
| 44 | 2775 | 119 | D | 2017-01-14 15:22:37 | 2017-01-14 15:22:37 | <null> | 1 |
| 45 | 2801 | 22 | C | 2017-01-14 15:22:40 | 2017-01-14 15:22:40 | <null> | 1 |
| 46 | 2734 | 1 | B | 2017-01-14 15:22:41 | 2017-01-14 15:22:41 | <null> | 1 |
| 47 | 2799 | 16 | C | 2017-01-14 15:22:41 | 2017-01-14 15:22:41 | <null> | 1 |
| 48 | 2782 | 2 | D | 2017-01-14 15:22:48 | 2017-01-14 15:25:11 | <null> | 1 |
| 49 | 2798 | 1 | B | 2017-01-14 15:22:51 | 2017-01-14 15:22:51 | <null> | 1 |
| 50 | 2803 | 21 | B | 2017-01-14 15:22:51 | 2017-01-14 15:22:51 | <null> | 1 |
| 51 | 2795 | 36 | B | 2017-01-14 15:22:54 | 2017-01-14 15:22:54 | <null> | 1 |
| 52 | 2803 | 22 | C | 2017-01-14 15:22:54 | 2017-01-14 15:22:54 | <null> | 1 |
| 53 | 2781 | 41 | D | 2017-01-14 15:22:59 | 2017-01-14 15:22:59 | <null> | 1 |
| 54 | 2862 | 321 | B | 2017-01-14 15:23:01 | 2017-01-14 15:23:01 | <null> | 2 |
| 55 | 2764 | 31 | A | 2017-01-14 15:23:01 | 2017-01-14 15:23:01 | <null> | 1 |
| 56 | 2769 | 21 | B | 2017-01-14 15:23:02 | 2017-01-14 15:23:02 | <null> | 1 |
| 57 | 2755 | 6 | A | 2017-01-14 15:23:02 | 2017-01-14 15:23:03 | <null> | 1 |
| 58 | 2871 | 302 | A | 2017-01-14 15:23:03 | 2017-01-14 15:27:40 | <null> | 2 |
| 59 | 2803 | 24 | E | 2017-01-14 15:23:03 | 2017-01-14 15:23:03 | <null> | 1 |
| 60 | 2868 | 302 | C | 2017-01-14 15:23:04 | 2017-01-14 15:23:04 | <null> | 2 |
| 61 | 2862 | 322 | E | 2017-01-14 15:23:13 | 2017-01-14 15:23:13 | <null> | 2 |
| 62 | 2799 | 17 | C | 2017-01-14 15:23:15 | 2017-01-14 15:23:15 | <null> | 1 |
| 63 | 2769 | 22 | C | 2017-01-14 15:23:16 | 2017-01-14 15:23:16 | <null> | 1 |
| 64 | 2741 | 3 | C | 2017-01-14 15:23:17 | 2017-01-14 15:23:17 | <null> | 1 |
select * from exams;
+------+---------------------+------------+----------+------------+-----------------+-----------------------------------------------------------------+--------------+---------------------+---------------------+
| id | applicated_at | board_id | active | duration | passing_grade | annotation | deleted_at | created_at | updated_at |
|------+---------------------+------------+----------+------------+-----------------+-----------------------------------------------------------------+--------------+---------------------+---------------------|
| 1 | 2017-01-14 09:00:00 | 32 | 1 | 180 | <null> | | <null> | 2017-01-12 21:22:08 | 2017-01-12 21:22:08 |
| 2 | 2017-01-14 09:05:00 | 32 | 1 | 180 | <null> | | <null> | 2017-01-12 21:25:14 | 2017-01-12 21:25:14 |
| 12 | 2017-01-19 16:00:00 | 13 | 1 | 180 | 120 | | <null> | 2017-01-17 19:44:00 | 2017-01-17 19:44:00 |
| 42 | 2017-02-01 16:00:00 | 41 | 1 | 250 | 0 | | <null> | 2017-01-30 12:40:21 | 2017-01-30 12:40:21 |
| 62 | 2017-03-03 11:00:00 | 48 | 1 | 60 | <null> | Biología Molecular 2 | <null> | 2017-01-31 20:59:19 | 2017-01-31 20:59:19 |
| 88 | 2017-03-03 12:30:00 | 49 | 1 | 70 | <null> | Citogenética 2 | <null> | 2017-02-01 04:49:44 | 2017-02-01 04:49:44 |
| 99 | 2017-02-17 17:00:00 | 28 | 1 | 120 | <null> | Examen de 130 reactivos | <null> | 2017-02-16 16:01:08 | 2017-02-16 16:01:08 |
| 109 | 2017-02-23 09:00:00 | 34 | 1 | 180 | <null> | Examen de 65 casos Neuro fisiología | <null> | 2017-02-21 18:37:57 | 2017-02-21 18:37:57 |
| 112 | 2017-02-25 09:00:00 | 47 | 1 | 180 | <null> | Examen de 61 casos en Tlatelolco | <null> | 2017-02-21 22:03:19 | 2017-02-21 22:03:19 |
| 121 | 2017-02-25 10:00:00 | 41 | 1 | 240 | <null> | Examen de 71 casos en Universisad Anuahuac | <null> | 2017-02-21 22:31:37 | 2017-02-21 22:31:37 |
| 123 | 2017-02-25 09:00:00 | 18 | 1 | 180 | <null> | Examen de 60 casos hematología adultos | <null> | 2017-02-22 13:50:20 | 2017-02-22 13:50:20 |Stack Overflow用户
发布于 2017-08-10 02:29:41
看起来你是在随机地尝试一些东西，而没有真正理解这些表是如何相互关联的，或者你想要什么数据。您需要系统地处理此问题，并了解数据的含义。不仅仅是随机地尝试连接。
我建议将其分解为步骤，然后添加每一条新信息，并一步一步地检查结果。这有助于您在遇到问题时隔离信息开始变得不再有意义的确切位置。如果您从一个检查和一个用户开始，并在删除这些条件之前先用一个小数据集弄清楚所有事情，这也是很有帮助的。
首先，您需要知道哪些问题属于哪个考试，以及正确答案是什么。因为在没有相互关系的情况下，您不需要问题和答案，因此这些问题和答案很可能需要内部联接。
然后，您需要向参加考试的用户添加一个联接。请注意，如果您要处理他们未回答的问题，则此连接需要针对考试而不是问题。因为您只对有用户回答的考试感兴趣，所以这需要是一个内部连接。
然后添加用户对考试中问题的答案。现在您使用左连接，因为参加考试的用户将会有一些未回答的问题。
然后，您可以将他们的答案与正确答案进行比较。
我不知道分心是什么，所以我没有包括这一点。不知何故，如果您需要它，您将需要了解这与其余内容的含义之间的关系。一旦你理解了它的意思，语法就变得清晰了。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/45597557
复制相关文章
相似问题
腾讯云开发者
