MYSQL错误结果输出

Question

我有三个表格，包含以下数据：

第一个名为“connections”的表格中，connections.username1表示关注者，connections.username2表示被关注者。

它具有以下行：

connections.username1 | connections.username2
      mikha           |          guy
      guy             |          maricela
      maricela        |          guy

第二个表名为questions。它有一个列用于提问者，名为questions.asker_username，另一个用于接收问题的人，名为questions.target_username。当提问者被称为"sys.tem"而目标被称为"every.one"时，它被认为是全局问题，可以由所有成员回答。

匿名用户可以提问，并将其IP记录为asker_username。

它具有以下行：

questions.id | questions.asker_username | questions.target_username | questions.question
  1          |      mikha               |       guy                 | what's your name?                             
  2          |      mikha               |       maricela            | What's your age?
  3          |      guy                 |       mikha               | what's your name?
  4          |      maricela            |       guy                 | favorite food?
  5          |      xx.xx.xxx.xx        |       mikha               | favorite pet?
  6          |      xx.xx.xxx.xx        |       guy                 | first name?
  7          |      xx.xx.xxx.xx        |       maricela            | first name?   
  8          |      sys.tem             |       every.one           | what's ur name?
  9          |      sys.tem             |       every.one           | favorite movie?  
 10          |      sys.tem             |       every.one           | favorite game? 

第三个表称为answers。答案表中的id与问题id相同。此表具有id、用户名和答案的列。

answers.id  |  answers.username | answers.answer
   1        |       guy         | my name is guy
   2        |     maricela      | my name is maricela
   3        |       mikha       | my name is mikha
   4        |       guy         | pizza        
   8        |       guy         | guy is my name
   8        |       maricela    | maricela is my name   
   9        |       maricela    | avatar

我想要一个查询，结合与“mikha”和他关注的人相关的以下条件：

1）questions.asker_username不是“mikha”

2）questions.target_username是“mikha”或他关注的任何用户之一。

3）如果questions.target_username等于“every.one”，并由“mikha”回答，请显示问题。

4）如果questions.target_username等于“every.one”，并由“mikha”关注的任何人回答，请显示问题及其答案。 如果没有“mikha”关注的用户回答，则不显示问题。

5）如果questions.target_username等于“every.one”，而且根本没有任何人回答，请仅显示一次该问题。

6）如果questions.target_username等于“every.one”，并且既未由“mikha”回答也未由他关注的任何人回答，则仅显示一次该问题。

我使用以下查询：

SELECT questions.id,answers.id,questions.asker_username,questions.target_username,
    answers.username,questions.question,answers.answer 
FROM questions 
    LEFT JOIN answers ON (questions.id = answers.id) 
    LEFT JOIN connections ON connections.username1 = 'mikha' 
        AND (questions.target_username = connections.username2 
            OR questions.asker_username = connections.username2 
            OR connections.username2 = answers.username) 
WHERE questions.asker_username <> 'mikha' 
    AND (questions.target_username = 'mikha' 
        OR questions.target_username = connections.username2 
        OR (questions.target_username = 'every.one' 
            AND (answers.username = 'mikha' 
                OR answers.username = connections.username2
                OR answers.username IS NULL)
            )
        ) 
GROUP BY questions.id,answers.username

我期望的结果：

questions.id | answers.id | questions.asker_username | questions.target_username | answers.username | questions.question | answers.answer
    3        |      3     |        guy               |          mikha            |    mikha         | what's your name?  | my name is mikha
    4        |      4     |        maricela          |          guy              |    guy           | favorite food?     | pizza
    5        |      5     |        xx.xx.xxx.xx      |          mikha            |    NULL          | favorite pet?      | NULL
    6        |      6     |        xx.xx.xxx.xx      |          guy              |    NULL          | first name?        | NULL        
    8        |      8     |        sys.tem           |         every.one         |    NULL          | what's ur name?    | NULL 
    8        |      8     |        sys.tem           |         every.one         |    guy           | what's ur name?    | guy is my name
    9        |      9     |        sys.tem           |         every.one         |    NULL          | favorite movie?    | NULL       
    10       |      10    |        sys.tem           |         every.one         |    NULL          | favorite game?     | NULL 

实际得到的结果：

 questions.id | answers.id | questions.asker_username | questions.target_username | answers.username | questions.question | answers.answer
    3        |      3     |        guy               |          mikha            |    mikha         | what's your name?  | my name is mikha
    4        |      4     |        maricela          |          guy              |    guy           | favorite food?     | pizza
    5        |      5     |        xx.xx.xxx.xx      |          mikha            |    NULL          | favorite pet?      | NULL
    6        |      6     |        xx.xx.xxx.xx      |          guy              |    NULL          | first name?        | NULL        
    8        |      8     |        sys.tem           |         every.one         |    guy           | what's ur name?    | guy is my name           
    10       |      10    |        sys.tem           |         every.one         |    NULL          | favorite game?     | NULL 

我在http://sqlfiddle.com/#!2/29929e/1上创建了一个方案，以展示实际获得的结果。

谢谢 :)

- Michael Samuel

---

什么是正确的查询语句以达到预期结果？这已经让我疯狂了一个星期 :( 谢谢 :) - Michael Samuel

需求4和6相互矛盾。第4个要求是：如果没有Mikha关注的人回答，则不显示答案。第6个要求是：如果没有Mikha关注的人回答，则显示答案。 - Daniel Hilgarth

这是一个全局问题，因此如果“mikha”关注了很多人，则即使该问题没有得到回答，该问题也会在动态中显示很多次。因此，我希望只为“mikha”显示一次，无论是否已回答，并在任何“mikha”关注的人回答时再次显示。谢谢 :) - Michael Samuel

只是一个小建议（它不能解决您的问题，但您可能想记住它）。使用用户名作为主键可能会有问题。如果用户想要更改他们的用户名怎么办？你必须改变每个引用该用户名的地方。并且，与整数相比，基于字符串值的查询和连接相对较慢。您可能希望拥有一个不可变的整数ID作为主键，并将用户名简单地作为唯一列。 - GordonM

@GordonM 我不知道有哪个网络应用程序可以让用户更改他用于登录的用户名...虽然我知道有一些应用程序使用displayname，并且可以更改... - shadyyx

Answer 1

好的，让我们从最简单的规则开始（您的第一个规则）：

SELECT q.id, a.id, q.asker_username, q.target_username, a.username, q.question, a.answer 
FROM questions q 
    LEFT JOIN answers a ON q.id = a.id  
WHERE q.asker_username <> 'mikha' 
GROUP BY q.id,a.username

现在让我们添加第二条规则——增加了更多的复杂性...

SELECT q.id, a.id, q.asker_username, q.target_username, a.username, q.question, a.answer 
FROM questions q 
    LEFT JOIN answers a ON q.id = a.id 
WHERE q.asker_username <> 'mikha' 
    AND q.target_username = 'mikha' 
        OR q.target_username IN (
            SELECT username2 
            FROM connections 
            WHERE username1 = 'mikha'
        )
GROUP BY q.id,a.username

现在是第三个规则（由mikha回答的所有人）：

SELECT q.id, a.id, q.asker_username, q.target_username, a.username, q.question, a.answer 
FROM questions q 
    LEFT JOIN answers a ON q.id = a.id 
WHERE q.asker_username <> 'mikha' 
    AND q.target_username = 'mikha' 
        OR q.target_username IN (
            SELECT username2 
            FROM connections 
            WHERE username1 = 'mikha'
        )
        OR (q.target_username = 'every.one' AND a.username = 'mikha')
GROUP BY q.id,a.username

现在是第四条规则：

SELECT q.id, a.id, q.asker_username, q.target_username, a.username, q.question, a.answer 
FROM questions q 
    LEFT JOIN answers a ON q.id = a.id 
WHERE q.asker_username <> 'mikha' 
    AND q.target_username = 'mikha' 
        OR q.target_username IN (
            SELECT username2 
            FROM connections 
            WHERE username1 = 'mikha'
        )
        OR (q.target_username = 'every.one' AND a.username = 'mikha')
        OR (q.target_username = 'every.one' AND a.username IN (
            SELECT username2 
            FROM connections 
                INNER JOIN answers ON answers.username = connections.username2
                    AND answers.answers IS NOT NULL
            WHERE username1 = 'mikha'
        ))
GROUP BY q.id,a.username

第五条规则（天哪！）：

SELECT q.id, a.id, q.asker_username, q.target_username, a.username, q.question, a.answer 
FROM questions q 
    LEFT JOIN answers a ON q.id = a.id 
WHERE q.asker_username <> 'mikha' 
    AND q.target_username = 'mikha' 
        OR q.target_username IN (
            SELECT username2 
            FROM connections 
            WHERE username1 = 'mikha'
        )
        OR (q.target_username = 'every.one' AND a.username = 'mikha')
        OR (q.target_username = 'every.one' AND a.username IN (
            SELECT username2 
            FROM connections 
                INNER JOIN answers ON answers.username = connections.username2
                    AND answers.answers IS NOT NULL
            WHERE username1 = 'mikha'
        ))
        OR (q.target_username = 'every.one' AND a.answer IS NULL)
GROUP BY q.id,a.username

最后一条：

SELECT q.id, a.id, q.asker_username, q.target_username, a.username, q.question, a.answer 
FROM questions q 
    LEFT JOIN answers a ON q.id = a.id 
WHERE q.asker_username <> 'mikha' 
    AND q.target_username = 'mikha' 
        OR q.target_username IN (
            SELECT username2 
            FROM connections 
            WHERE username1 = 'mikha'
        )
        OR (q.target_username = 'every.one' AND a.username = 'mikha')
        OR (q.target_username = 'every.one' AND a.username IN (
            SELECT username2 
            FROM connections 
                INNER JOIN answers ON answers.username = connections.username2
                    AND answers.answers IS NOT NULL
            WHERE username1 = 'mikha'
        ))
        OR (q.target_username = 'every.one' AND a.answer IS NULL)
        OR (q.target_username = 'every.one' AND a.username NOT IN (
            SELECT username2 
            FROM connections 
                INNER JOIN answers ON answers.username = connections.username2
                    AND answers.answers IS NOT NULL
            WHERE username1 = 'mikha'
        ))
GROUP BY q.id,a.username

我认为规则4和规则6有些相互抵触（可以说是相互矛盾），如果在一个查询中使用，它们的效果将与省略它们相同...

我没有测试任何查询，但我相信它们有效。

- shadyyx

---

嘿，Shady :) 谢谢你的努力，但是它给我相同的输出，而且第一个案例现在由于某种原因被忽略了。你可以在这里测试sqlfiddle http://sqlfiddle.com/#!2/29929e/101 谢谢 :) - Michael Samuel

@MichaelSamuel 不好意思，我没有太多时间来测试查询并查找缺失的部分。请按照我的示例-“将您的查询解构为最简单的部分，并像我一样逐个添加它们，直到找到所需的结果为止。” :-) 在构建复杂查询时始终使用此技术-从最简单的一个开始，逐步构建一个步骤。 - shadyyx

相信我...我已经尝试了一个星期，但是没有任何进展 :( 在查询中有些东西我还是缺失的。我无法实现这个目标，所以不得不寻求帮助..无论如何，还是谢谢 :) - Michael Samuel

Answer 2

问题在于，在某些情况下，即使只有一个匹配的答案存在，您也希望显示两次问题。我引用以下内容：

“因此，我想仅为“ mikha”显示一次，无论它是否已回答，并在任何“ mikha”关注的人回答时再次显示它。”

这种重复使事情变得非常困难。

我尝试使用UNION解决这个问题，看起来似乎可以解决。但是，我仍然没有完全理解您的要求...

不管怎样，我们开始吧：

select * from
(
  select
    q.id as q_id, a.id as a_id, q.asker_username,
    q.target_username, a.username, q.question, a.answer
  from
    questions q
    left outer join answers a on q.id = a.id
  where
    q.asker_username <> 'mikha' 
    and
    (
      q.target_username = 'mikha'
      or q.target_username in
         (select username2 from connections where username1 = 'mikha')
      or
      (
        q.target_username = 'every.one'
        and
        (
          a.username = 'mikha'
          or a.username in
             (select username2 from connections where username1 = 'mikha')
          or a.id is null
        )
      )
    )
  union
  select
    q.id as q_id, NULL as a_id, q.asker_username,
    q.target_username, NULL, q.question, NULL
  from
    questions q
  where
    q.asker_username <> 'mikha' 
    and q.target_username = 'every.one'
    and not exists (select id
                    from answers
                    where
                      id = q.id
                      and username = 'mikha'
                    )
) r
order by q_id;

现场测试：
使用mikha的答案进行第8个问题的测试
不使用mikha的答案进行第8个问题的测试

- Daniel Hilgarth

---

我犯了一个错，刚刚编辑了我的原始问题。第8行已经被Mikha回答过了。为避免混淆，我将其删除。现在第8行应该显示两次，一次是为了Mikha，因为他还没有回答，另一次是为了Guy，因为他后面跟着Mikha并且回答了。Maricela的回答被忽略了，因为她不是Mikha关注的人。 - Michael Samuel

这个很好用 :)，但是有没有不使用联合和子查询的方法来执行上面的查询？在扩展时，这两种方法都会影响性能。谢谢 :) - Michael Samuel

@MichaelSamuel：我认为没有其他方法。 - Daniel Hilgarth

@MichaelSamuel：UNION不仅仅是追加内容，还有其他功能 - 那应该是UNION ALL。此外，UNION还隐含了去重的功能。 - Daniel Hilgarth

@MichaelSamuel：使用JOIN操作可能会使结果数量增加，但我不确定结果是否相同。你可以尝试一下。如果可以，那就太好了 :-) - Daniel Hilgarth

Answer 3

我认为你的起点主要问题之一是需要将连接表连接两次，一次用于答案，一次用于问题。

下面的代码对它正在做什么进行了内联注释。我也同意其他答案建议使用数字ID字段进行比较 - 我还建议在答案表上放置唯一的 rowid 字段（更好的是，将 id 更改为 question_id ，然后使 id 成为唯一字段）。

SELECT DISTINCT q.id,a.id,q.asker_username,q.target_username,a.username,q.question,a.answer
FROM questions q
  /* Answers */
  LEFT JOIN answers a ON (q.id=a.id)
  /* connection entries where the person being followed is the target */
  LEFT JOIN (SELECT username2 FROM connections WHERE username1='mikha') c_q
    ON c_q.username2=q.target_username
  /* connection entries where the person being followed answered the question */
  LEFT JOIN (SELECT username2 FROM connections WHERE username1='mikha') c_a
    ON c_a.username2=a.username 
  /* Own answers */
  LEFT JOIN (SELECT id FROM answers WHERE username='mikha') a_own 
    ON (q.id=a_own.id) 
  WHERE 
  /* Asker not mikha, target is mikha or followed user - rules 1,2 */
  (q.asker_username <> 'mikha'
  AND (q.target_username='mikha' OR c_q.username2 IS NOT NULL))
  OR
  /* sys.tem/every.one, answered by mikha - rule 3 */
  (q.target_username='every.one' AND a.username='mikha')
  OR
  /* Rules 4, 5 & 6 combine to give "show the answer at least once, and once for every followed user who answered" - here we select any every.one messages where mikha didn't answer and nor did the people he follows */
  (q.target_username='every.one' AND a_own.id IS NULL AND c_a.username2 IS NULL);

它不会为问题8给您两行，但我真的不知道您为什么需要那样做。问题在于，您的规则中哪些是加法规则，哪些是组合规则并不清楚。如果您能解释一下哪些规则应该“添加”一行而不仅仅是提供另一个显示行的原因，也许我们可以为您解决问题8的额外行。

- almcnicoll

---

好的，简单来说，问题主要出现在问题是全局的情况下（目标=每个人）。用户（mikha）可能会关注很多用户，比如1000个用户。在这种情况下，全局问题将在mikha的动态中出现1000次，这就没有意义了。这就是为什么我想要只在回答时显示它，以避免重复。我还想再显示一次，以便“mikha”可以回答它，或者如果它被mikha回答了，则显示带有答案的问题（这就是为什么要显示第8行两次，一次是为了mikha，一次是因为被mikha关注的人回答了它）。 - Michael Samuel

另一种情况是当全站没有任何成员回答全局问题时，显示一次以便“mikha”可以回答（这是第10行）。另一种情况是当问题既不被mikha回答也不被他关注的人回答时，也显示一次以便mikha可以回答（这是第9行）。 - Michael Samuel