MYSQL错误结果输出

Question

我有3个表，其中包含以下数据：

名为connections的第一个表，其中connections.username1是跟随的表，connections.username2是被跟随的表。

它包含以下行：

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

第二个表称为问题。它有一个针对提问者的列，称为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上建立了一个方案，向你展示我实际得到的结果

谢谢:)

Answer 1

问题是，在某些情况下，当只有一个匹配的答案存在时，您希望将问题显示两次。我引述如下：

所以，我只想为"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;

现场测试：

With answer from mikha for question 8

Without answer from mikha for question 8

Answer 2

好的，让我们从最简单的开始(你的第一条规则)：

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是相互矛盾的(可以说是矛盾的)，当在一个查询中使用它时，它的效果就像被省略了一样……

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

Answer 3

我建议你的出发点的一个主要问题是，你需要加入你的连接表两次-一次是为了回答，一次是为了问题。

下面的代码给出了它正在做的事情的内联注释。我也同意其他答案的建议，即数字id字段更适合用于比较-我还建议在answers表中放置一个唯一的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中获得额外的行。