一个麻烦的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

如果没有看到样本数据和所需的输出，很难判断，但IMHO您的查询可能看起来像这个

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演示