php - MYSQL错误结果输出

Question

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

第一个表称为连接，其中 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”没有回答，也没有他关注的任何人回答，则只显示一次问题。

p>

我使用以下查询:

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

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

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 有点相互矛盾(可以说是矛盾的)，当在一个查询中使用时，它的效果与省略时相同......

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