GROUP_BY只在值等于时显示首次出现
I have a table called questions with the following row:
我有一个叫做问题的表格，列如下:
questions.id | questions.target_username
1 | every.one
2 | every.one
I have also the answers table with the following rows:
我还有以下几行答案表:
answers.id | answers.username
1 | guy
1 | maricela
2 | mikha
As you can see, the same question id could have answers from different users. I want to select only rows if:
正如您所看到的，相同的问题id可以有来自不同用户的答案。我只选择以下行:
a) question has answers that don't include (mikha) as answers.username and in this case display the question id once only
a)问题的答案不包括(mikha)作为答案。在本例中，用户名和问题id只显示一次
b) question has the answer username (mikha) as answers.username.
问题的答案是用户名(mikha)，用户名。
I use the following query:
我使用以下查询:
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
Result I expect:
结果我期望:
questions.id | answers.usernme
1 |
2 | mikha
What I actually get:
我得到什么:
questions.id | answers.usernme
1 | guy
1 | maricela
2 | mikha
Thanks in advance Regards Micahel
米迦黑尔，先谢
4 个解决方案
#1
3
You just need to move the answers.username = 'mikha' condition to the on clause, at which point you can even remove the redundant where conditions.
你只需要移动答案。用户名= 'mikha'条件到on子句，在该条件下，您甚至可以删除多余的条件。
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;
Edit: I'm not sure if this is a requirement or not, but if you want to omit questions that have not had anyone answer them yet, the following is what you would want:
编辑:我不确定这是否是一个要求，但如果你想省略那些还没有人回答的问题，你想要的是:
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;
Edit 2: Here's what I have in my test tables, and the results from query #2:
编辑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)
Edit 3: Here are updated table data and an updated query that complies with the criteria you added in the comments:
编辑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)
New query:
新的查询:
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;
Result:
结果:
+----+----------+
| id | username |
+----+----------+
| 1 | NULL |
| 2 | mikha |
| 4 | mikha |
+----+----------+
3 rows in set (0.00 sec)