mysql - 一个麻烦的 MySQL Join 操作

Question

两个表，一个包含问题，另一个包含每个用户的回答(如果他们已回答)。我正在尝试提取按权重(最重要)排序的下一个问题，其中用户 a 在答案表中没有答案。

我的表格如下所示:

table: app_questions
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| owneruid | int(11)      | YES  |     | NULL    |                |
| question | varchar(350) | YES  |     | NULL    |                |
| answer_1 | varchar(350) | YES  |     | NULL    |                |
| answer_2 | varchar(350) | YES  |     | NULL    |                |
| answer_3 | varchar(350) | YES  |     | NULL    |                |
| answer_4 | varchar(350) | YES  |     | NULL    |                |
| weight   | decimal(5,2) | YES  |     | NULL    |                |
| datetime | datetime     | YES  |     | NULL    |                |

table: app_answers
| id                 | int(11)      | NO   | PRI | NULL    | auto_increment |
| uid                | int(11)      | YES  |     | NULL    |                |
| quid               | int(11)      | YES  |     | NULL    |                |
| answer             | int(11)      | YES  |     | NULL    |                |
| importance         | int(11)      | YES  |     | NULL    |                |
| answer_status      | varchar(20)  | YES  |     | NULL    |                |
| answer_explanation | varchar(200) | YES  |     | NULL    |                |
| datetime           | datetime     | YES  |     | NULL    |                |

我尝试了一些变体，但尚未得到我所需要的:

   SELECT  uid ,
        question ,
        answer_1 ,
        answer_2 ,
        answer_3 ,
        answer_4 ,
        weight ,
        answer_status
FROM    match_questions
        LEFT JOIN match_answers ON match_questions.id = match_answers.quid
WHERE   answer_status IS NULL
        AND EXISTS ( SELECT *
                     FROM   match_answers
                     WHERE  uid = 1 ) 
ORDER BY weight DESC LIMIT 1;   



    SELECT uid ,
        question ,
        answer_1 ,
        answer_2 ,
        answer_3 ,
        answer_4 ,
        weight ,
        answer_status
 FROM   match_questions
        LEFT JOIN match_answers ON match_questions.id = match_answers.quid
 WHERE  uid IS NULL
        AND answer_status IS NULL 
 ORDER BY weight DESC LIMIT 1;




 SELECT  uid ,
            question ,
            answer_1 ,
            answer_2 ,
            answer_3 ,
            answer_4 ,
            weight ,
            answer_status
    FROM    match_questions
            LEFT JOIN match_answers ON match_questions.id = match_answers.quid
    WHERE   answer_status IS NULL
            AND uid IS NULL 
    ORDER BY weight DESC LIMIT 1;

感谢任何指导。

Answer 1

如果没有看到示例数据和所需的输出，很难判断，但恕我直言，您的查询可能如下所示

SELECT q.id,
       q.question,
       q.answer_1,
       q.answer_2,
       q.answer_3,
       q.answer_4,
       q.weight
  FROM match_questions q LEFT JOIN match_answers a
    ON q.id = a.quid
   AND a.uid = 1
 WHERE a.quid IS NULL
 ORDER BY q.weight, q.id
 LIMIT 1

这里是SQLFiddle 演示