MySQL 查询以获得所需的输出

Question

我当前查询得到的结果:

+---------------+--------------------+-------+--------+--------+
|    Name       |       email        | Apple | Orange | Banana |
+---------------+--------------------+-------+--------+--------+
| NULL          | NULL               | YES   | YES    | YES    |
| Molly Weasley | mweasley@gmail.com | YES   | YES    | YES    |
| Viktor Krum   | vkrum@gmail.com    | YES   | YES    | NULL   |
+---------------+--------------------+-------+--------+--------+

我想要什么:

+---------------+--------------------+-------+--------+--------+--------------+
|    Name       |       email        | Apple | Orange | Banana | Info Entered |
+---------------+--------------------+-------+--------+--------+--------------+
| Severus Snape | ssnape@gmail.com   |       |        |        | No           |
| Molly Weasley | mweasley@gmail.com | YES   | YES    | YES    | Yes          |
| Viktor Krum   | vkrum@gmail.com    | YES   | YES    |        | Yes          |
+---------------+--------------------+-------+--------+--------+--------------+

基本上，西弗勒斯·斯内普还没有回答问卷(他的答案表中没有记录)，但我仍然想向他展示(因为他收到了问题表中所示的问卷)。苹果、橙子和香蕉对他来说是空白的(由于某种原因，它一直显示"is")。

我正在使用的查询:

SELECT CONCAT(people.firstName, ' ', people.lastName) AS Name, 
people.email, 
MAX(CASE WHEN questions.fruit = 'apple' THEN 'YES' END) AS Apple, 
MAX(CASE WHEN questions.fruit = 'orange' THEN 'YES' END) AS Orange, 
MAX(CASE WHEN questions.fruit = 'banana' THEN 'YES' END) AS Banana 
FROM answers 
LEFT JOIN questions ON answers.questionID = questions.questionID 
LEFT JOIN people ON questions.person = people.person AND questions.questionnaire = '24' 
GROUP BY CONCAT(people.firstName, ' ', people.lastName), people.email 
ORDER BY Name

示例数据:

人员表:

+-----------+----------+--------------------+--------+
| firstName | lastName |       email        | person |
+-----------+----------+--------------------+--------+
| Harry     | Potter   | hpotter@gmail.com  |      1 |
| Ron       | Weasley  | rweasley@gmail.com |      2 |
| Hermione  | Granger  | hgranger@gmail.com |      3 |
| Severus   | Snape    | ssnape@gmail.com   |      4 |
| Viktor    | Krum     | vkrum@gmail.com    |      5 |
| Molly     | Weasley  | mweasley@gmail.com |      6 |
| Oliver    | Wood     | owood@gmail.com    |      7 |
| Remus     | Loopin   | rlupin@gmail.com   |      8 |
+-----------+----------+--------------------+--------+

问题表:

+---------------+--------+------------+--------+
| questionnaire | person | questionID | fruit  |
+---------------+--------+------------+--------+
|            23 |      1 |         55 | apple  |
|            23 |      1 |         56 | orange |
|            23 |      1 |         57 | banana |
|            23 |      2 |         58 | apple  |
|            23 |      2 |         59 | orange |
|            23 |      2 |         60 | banana |
|            23 |      3 |         61 | apple  |
|            23 |      3 |         62 | orange |
|            23 |      3 |         63 | banana |
|            24 |      4 |         64 | apple  |
|            24 |      4 |         65 | orange |
|            24 |      4 |         66 | banana |
|            24 |      5 |         67 | apple  |
|            24 |      5 |         68 | orange |
|            24 |      5 |         69 | banana |
|            24 |      6 |         70 | apple  |
|            24 |      6 |         71 | orange |
|            24 |      6 |         72 | banana |
+---------------+--------+------------+--------+

答案表:

+----------+------------+---------------------+-----------------+
| answerID | questionID |        info         |  dateAnswered   |
+----------+------------+---------------------+-----------------+
|       40 |         59 | some info here      | 5/26/2017 19:23 |
|       41 |         59 |                     | 5/26/2017 18:30 |
|       42 |         59 |                     | 5/26/2017 18:29 |
|       43 |         66 |                     | 5/26/2017 18:36 |
|       44 |         66 |                     | 5/26/2017 20:28 |
|       45 |         70 |                     | 5/26/2017 20:28 |
|       46 |         55 | more info here      | 5/26/2017 20:29 |
|       47 |         71 |                     | 5/26/2017 20:29 |
|       48 |         67 |                     | 5/26/2017 20:31 |
|       49 |         64 |                     | 5/26/2017 18:37 |
|       50 |         55 |                     | 5/26/2017 18:46 |
|       51 |         72 |                     | 5/26/2017 18:46 |
|       52 |         72 | another bit of info | 5/26/2017 18:46 |
|       53 |         72 | and more info       | 5/26/2017 18:46 |
|       54 |         61 |                     | 5/26/2017 18:29 |
|       55 |         61 |                     | 5/26/2017 18:30 |
|       56 |         68 | the info            | 5/26/2017 18:36 |
|       57 |         59 |                     | 5/26/2017 19:22 |
|       58 |         66 |                     | 5/26/2017 19:37 |
|       59 |         61 |                     | 5/26/2017 19:37 |
|       60 |         61 |                     | 5/26/2017 18:33 |
|       61 |         68 | this info           | 5/26/2017 18:38 |
|       62 |         68 |                     | 5/26/2017 19:33 |
|       63 |         68 | some more  info     | 5/26/2017 19:42 |
|       64 |         68 |                     | 5/26/2017 19:56 |
|       65 |         60 |                     | 5/26/2017 20:03 |
+----------+------------+---------------------+-----------------+

Answer 1

尝试更改顺序:

SELECT 
    CONCAT(people.firstName, ' ', people.lastName) AS Name, 
    people.email, 
    MAX(CASE WHEN questions.fruit = 'apple' AND answers.questionID IS NOT NULL THEN 'YES' END) AS Apple, 
    MAX(CASE WHEN questions.fruit = 'orange' AND answers.questionID IS NOT NULL  THEN 'YES' END) AS Orange, 
    MAX(CASE WHEN questions.fruit = 'banana' AND answers.questionID IS NOT NULL  THEN 'YES' END) AS Banana 
FROM people 
JOIN questions ON questions.person = people.person AND questions.questionnaire = '24' 
LEFT JOIN answers ON answers.questionID = questions.questionID 
GROUP BY CONCAT(people.firstName, ' ', people.lastName), people.email 
ORDER BY Name