MySQL查询在不同条件下跨4个表连接

我有 4 个表(为简洁起见，已精简为相关列):

CREATE TABLE `papers` (
   `paper_id` int(11) NOT NULL DEFAULT '0',
   PRIMARY KEY (`paper_id`)
);
INSERT INTO papers ( paper_id ) VALUES(1001);
INSERT INTO papers ( paper_id ) VALUES(1002);
INSERT INTO papers ( paper_id ) VALUES(1003);
INSERT INTO papers ( paper_id ) VALUES(1004);
INSERT INTO papers ( paper_id ) VALUES(1005);
INSERT INTO papers ( paper_id ) VALUES(1006);

CREATE TABLE `questions` (
  `question_id` int(11) NOT NULL AUTO_INCREMENT,
  `type_id` int(11) NOT NULL,
  PRIMARY KEY (`question_id`)
);
INSERT INTO questions ( type_id ) VALUES(1);
INSERT INTO questions ( type_id ) VALUES(2);
INSERT INTO questions ( type_id ) VALUES(1);
INSERT INTO questions ( type_id ) VALUES(3);

CREATE TABLE `question_depends` (
  `question_id` int(11) NOT NULL,
  `depends_question_id` int(11) NOT NULL,
  `depends_answer_val` int(11) NOT NULL,
  PRIMARY KEY (`question_id`,`depends_question_id`)
);
INSERT INTO question_depends ( question_id, depends_question_id, depends_answer_val ) VALUES(3, 1, 0);
INSERT INTO question_depends ( question_id, depends_question_id, depends_answer_val ) VALUES(2, 1, 1);
INSERT INTO question_depends ( question_id, depends_question_id, depends_answer_val ) VALUES(3, 1, 1);

CREATE TABLE `answers` (
  `paper_id` int(11) NOT NULL,
  `question_id` int(11) NOT NULL,
  `answer_val` int(2) NOT NULL,
  PRIMARY KEY (`paper_id`,`question_id`)
);
INSERT INTO answers ( paper_id, question_id, answer_val ) VALUES(1002, 1, 1);
INSERT INTO answers ( paper_id, question_id, answer_val ) VALUES(1002, 4, 0);
INSERT INTO answers ( paper_id, question_id, answer_val ) VALUES(1004, 1, 0);
INSERT INTO answers ( paper_id, question_id, answer_val ) VALUES(1004, 3, 1);
INSERT INTO answers ( paper_id, question_id, answer_val ) VALUES(1005, 1, 1);

我想提出一个查询，显示所有可能组合的所有数据:

所有论文的 ID 应该至少输出一次

给定的 paper_id 可能有也可能没有答案，可能有也可能没有依赖关系

最终目标是查看是否每个依赖问题都得到了回答，如果回答 val 与每个论文 id 的依赖回答 val 匹配，并确定 paper_id 是否有依赖问题，以及是否有任何问题没有得到回答(无论是不是他们有依赖关系)

如果需要，我可以调整表/数据

我很接近:

select P.paper_id as P_PID,
  A.paper_id as A_PID,
  A.question_id as A_QID,
  A.answer_val as A_VAL,
  QD.question_id as QD_QID,
  QD.depends_question_id AS QD_DQID,
  QD.depends_answer_val AS QD_VAL,
  Q.type_id AS Q_TYPE
from papers P
left join answers A on A.paper_id = P.paper_id
left join question_depends QD on QD.depends_question_id = A.question_id
left join questions Q on Q.question_id = QD.question_id
UNION
select NULL AS P_PID,
  NULL AS A_PID,
  A.question_id as A_QID,
  A.answer_val as A_VAL,
  QD.question_id as QD_QID,
  QD.depends_question_id AS QD_DQID,
  QD.depends_answer_val AS QD_VAL,
  Q.type_id AS Q_TYPE
from question_depends QD
left join answers A on QD.depends_question_id = A.question_id
left join questions Q on Q.question_id = QD.question_id
  where A.question_id IS NULL

...但输出具有每个 paper_id 每一行的答案数据，而不仅仅是该 paper_id 的答案数据。任何想法表示赞赏!使用这个小样本数据集上面的选择输出:

P_PID   A_PID   A_QID   A_VAL   QD_QID  QD_DQID QD_VAL  Q_TYPE
1001
1002    1002    1       1       2       1       1       2
1002    1002    1       1       3       1       0       1
1002    1002    4       0       NULL    NULL    NULL    NULL
1003    NULL    NULL    NULL    NULL    NULL    NULL    NULL
1004    1004    1       0       2       1       1       2
1004    1004    1       0       3       1       0       1
1004    1004    3       1       NULL    NULL    NULL    NULL
1005    1005    1       1       2       1       1       2
1005    1005    1       1       3       1       0       1
1006    NULL    NULL    NULL    NULL    NULL    NULL    NULL

理想的输出(如果我没有打错的话)是:

P_PID   A_PID   A_QID   A_VAL   QD_QID  QD_DQID QD_VAL  Q_TYPE
1001    NULL    NULL    NULL    2       1       1       2
1001    NULL    NULL    NULL    3       1       0       1
1002    1002    1       1       2       1       1       2
1002    NULL    NULL    NULL    3       1       0       1
1002    1002    4       0       NULL    NULL    NULL    3
1003    NULL    NULL    NULL    2       1       1       2
1003    NULL    NULL    NULL    3       1       0       1
1004    1004    1       0       2       1       1       2
1004    NULL    NULL    NULL    3       1       0       1
1004    1004    3       1       NULL    NULL    NULL    1
1005    1005    1       1       2       1       1       2
1005    NULL    NULL    NULL    3       1       0       1
1006    NULL    NULL    NULL    2       1       1       2
1006    NULL    NULL    NULL    3       1       0       1

最佳答案

“分而治之”。把问题分成三种情况。

 SELECT /*Answers and no depends*/ p.paper_id AS p_pid,
  a.paper_id AS a_pid,
  a.question_id AS a_qid,
  a.answer_val AS a_val,
  qd.question_id AS qd_qid,
  qd.depends_question_id AS qd_dqid,
  qd.depends_answer_val AS qd_val,
  q.type_id AS q_type
 FROM papers p
 JOIN answers a
    ON a.paper_id = p.paper_id
 LEFT OUTER JOIN question_depends qd
    ON  a.question_id = qd.depends_question_id
        AND
        a.answer_val = qd.depends_answer_val
 LEFT OUTER JOIN questions q
    ON q.question_id = a.question_id
 WHERE qd.question_id IS NULL
UNION
SELECT /*Answers and depends*/ p.paper_id AS p_pid,
  a.paper_id AS a_pid,
  a.question_id AS a_qid,
  a.answer_val AS a_val,
  qd.question_id AS qd_qid,
  qd.depends_question_id AS qd_dqid,
  qd.depends_answer_val AS qd_val,
  q.type_id AS q_type
 FROM papers p
 JOIN answers a
    ON a.paper_id = p.paper_id
 LEFT OUTER JOIN question_depends qd
    ON  a.question_id = qd.depends_question_id
        AND
        a.answer_val = qd.depends_answer_val
 LEFT OUTER JOIN questions q
    ON q.question_id = qd.question_id
 WHERE qd.question_id IS NOT NULL
UNION
SELECT /*Missing answer*/ p.paper_id AS p_pid,
  a.paper_id AS a_pid,
  a.question_id AS a_qid,
  a.answer_val AS a_val,
  qd.question_id AS qd_qid,
  qd.depends_question_id AS qd_dqid,
  qd.depends_answer_val AS qd_val,
  q.type_id AS q_type
 FROM papers p
 CROSS JOIN question_depends qd
 JOIN questions q
    ON q.question_id = qd.question_id
 LEFT OUTER JOIN answers a
    ON a.paper_id = p.paper_id
        AND
        a.question_id = qd.depends_question_id
        AND
        a.answer_val = qd.depends_answer_val
 WHERE a.question_id IS NULL
 ORDER BY 1, 7 DESC;

您可以在 SQL Fiddle http://sqlfiddle.com/#!9/fbd3a9/3 上查看结果

关于MySQL查询在不同条件下跨4个表连接，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/45947127/