php - GROUP_BY 仅在值等于时显示第一次出现

Question

我有一个名为 questions 的表，其中包含以下行:

questions.id | questions.target_username
   1         | every.one
   2         | every.one

我还有包含以下行的答案表:

answers.id | answers.username       
   1       | guy
   1       | maricela 
   2       | mikha 

如您所见，同一个问题 ID 可能有不同用户的答案。在以下情况下，我只想选择行:

a) 问题的答案不包括 (mikha) 作为 answers.username 并且在这种情况下只显示一次问题 id

b) 问题的答案用户名 (mikha) 作为 answers.username。

我使用以下查询:

SELECT questions.id, answers.username
FROM questions
LEFT JOIN answers ON ( questions.id = answers.id ) 
WHERE questions.target_username =  'every.one'
AND (
answers.username IS NOT NULL 
OR answers.username =  'mikha'
)
GROUP BY questions.id, answers.username

我期望的结果:

questions.id | answers.usernme
   1         |  
   2         | mikha

我实际得到的:

questions.id | answers.usernme
   1         | guy
   1         | maricela  
   2         | mikha

提前致谢问候迈克尔

Answer 1

您只需将 answers.username = 'mikha' 条件移动到 on 子句，此时您甚至可以删除多余的 where 条件。

SELECT questions.id, answers.username
FROM questions
LEFT JOIN answers ON(questions.id = answers.id AND answers.username = 'mikha')
WHERE questions.target_username = 'every.one'
GROUP BY questions.id, answers.username;

编辑:我不确定这是否是一项要求，但如果您想省略尚未有人回答的问题，以下是您想要的:

SELECT questions.id, answers.username
FROM questions
LEFT JOIN answers ON(questions.id = answers.id AND answers.username = 'mikha')
LEFT JOIN answers others ON(questions.id = others.id AND others.username <> 'mikha')
WHERE questions.target_username = 'every.one'
AND (
    (
        answers.id IS NOT NULL
        AND others.id IS NULL
    )
    OR (
        answers.id IS NULL
        AND others.id IS NOT NULL
    )
)
GROUP BY questions.id, answers.username;

编辑 2:这是我的测试表中的内容，以及查询 #2 的结果:

SELECT * FROM questions;
+----+-----------------+
| id | target_username |
+----+-----------------+
|  1 | every.one       |
|  2 | every.one       |
|  3 | every.one       |
|  4 | every.one       |
+----+-----------------+
4 rows in set (0.00 sec)

SELECT * FROM answers;
+-----+----+----------+
| ida | id | username |
+-----+----+----------+
|   1 |  1 | guy      |
|   2 |  1 | maricela |
|   3 |  2 | mikha    |
+-----+----+----------+
3 rows in set (0.00 sec)

(Run query #2 above)
+----+----------+
| id | username |
+----+----------+
|  1 | NULL     |
|  2 | mikha    |
+----+----------+
2 rows in set (0.00 sec)

编辑 3:这是更新的表数据和更新的查询，符合您在评论中添加的条件:

SELECT * FROM questions;
+----+-----------------+
| id | target_username |
+----+-----------------+
|  1 | every.one       |
|  2 | every.one       |
|  3 | every.one       |
|  4 | every.one       |
+----+-----------------+
4 rows in set (0.00 sec)

SELECT * FROM answers;
+-----+----+----------+
| ida | id | username |
+-----+----+----------+
|   1 |  1 | guy      |
|   2 |  1 | maricela |
|   3 |  2 | mikha    |
|   7 |  4 | guy      |
|   8 |  4 | mikha    |
+-----+----+----------+
5 rows in set (0.00 sec)

新查询:

SELECT questions.id, answers.username
FROM questions
LEFT JOIN answers ON(questions.id = answers.id AND answers.username = 'mikha')
LEFT JOIN answers others ON(questions.id = others.id AND others.username <> 'mikha')
WHERE questions.target_username = 'every.one'
AND (
    answers.username = 'mikha'
    OR (
        answers.id IS NULL
        AND others.id IS NOT NULL
    )
)
GROUP BY questions.id, answers.username;

结果:

+----+----------+
| id | username |
+----+----------+
|  1 | NULL     |
|  2 | mikha    |
|  4 | mikha    |
+----+----------+
3 rows in set (0.00 sec)