mysql - 单个 MySQL 表内的复杂比较和排除

Question

MySQLfiddle在这里:http://sqlfiddle.com/#!2/6094e8/6

fiddle 包含与我下面的示例相关的数据。

我正在尝试使用一个表:

表1:user_answers表(存储用户对各种问题的答案及其可接受的答案)

存储的值得注意的值是:

用户 ID(uid 列)
他们正在回答的问题的问题 ID(quid 列)
问题的答案(answer 列)
可接受的答案存储 #1 存储该用户的其他可接受的答案(acceptable_1 列)
可接受的答案存储 #2 存储该用户的其他可接受的答案(acceptable_2 列)
可接受的答案存储 #3 存储该用户的其他可接受的答案(acceptable_3 列)
可接受的答案存储 #4 存储该用户的其他可接受的答案(acceptable_4 列)
他们的答案的重要性(重要性列)

答案列将存储 1 到 4 之间的值。

可接受的列包含与其列位置一致的选定可接受答案。例如，用户 1 回答 1，但会接受答案 2 和 3。这将使 acceptable_2 = 2 和 acceptable_3 = 3，留下 acceptable_1和值为 0 的 acceptable_2。

为了举例，我将尝试解释我正在寻找的结果:

示例1:
用户 1 在 1 英镑上回答了 3，并且会接受其他用户针对同一问题的答案 2 和 4。

用户 2 在 1 英镑上回答 2，并且会接受其他用户针对同一问题的回答 3

示例 1 的结果:用户 1 将接受用户 2 的答案，用户 2 将接受用户 1 的答案。该结果应从结果中排除。

示例 2:用户 1 在 2 英镑上回答了 2，并且会接受其他用户针对同一问题的答案 1 或 4。

用户 2 在 2 英镑上回答了 2，并且会接受其他用户针对同一问题的回答 2 或 4。

示例 2 的结果:用户1不会接受用户2的答案，但用户2会接受用户1的答案。此比较有一个冲突，应包含在结果中。

示例 3:用户 1 在第 3 英镑上回答了 1，并且会接受其他用户针对同一问题的回答 1 或 2。

用户 2 在第 3 英镑上回答了 1，并且会接受其他用户针对同一问题的回答 1 或 2。

示例 3 的结果:用户 1 会接受用户 2 的答案，用户 2 也会接受用户 1 的答案。不存在冲突，该结果应从结果中排除。

示例 4:用户 1 在第 4 英镑上回答了 1，并且会接受其他用户针对同一问题的回答 1 或 2。

用户 2 在 4 英镑上回答了 3，并且会接受其他用户针对同一问题的回答 3 或 4。

示例 4 的结果:用户1不会接受用户2的答案，用户2也不会接受用户1的答案。有两个冲突，这个结果应该包含在结果中。

正如我希望可见的那样，我想获取两个用户已回答的问题的quid，这些问题的答案与其他用户可接受的列中的内容存在冲突。

到目前为止，我已经能够使用此查询收集每个用户的答案和可接受的答案:

select ua.quid,
       GROUP_CONCAT(IF(uid=1,answer,'') SEPARATOR '') as a1,
       GROUP_CONCAT(IF(uid=2,answer,'') SEPARATOR '') as a2,
       GROUP_CONCAT(IF(uid=1,acceptable_1,'') SEPARATOR '') as ac1_1,
       GROUP_CONCAT(IF(uid=1,acceptable_2,'') SEPARATOR '') as ac2_1,
       GROUP_CONCAT(IF(uid=1,acceptable_3,'') SEPARATOR '') as ac3_1,
       GROUP_CONCAT(IF(uid=1,acceptable_4,'') SEPARATOR '') as ac4_1,
       GROUP_CONCAT(IF(uid=2,acceptable_1,'') SEPARATOR '') as ac1_2,
       GROUP_CONCAT(IF(uid=2,acceptable_2,'') SEPARATOR '') as ac2_2,
       GROUP_CONCAT(IF(uid=2,acceptable_3,'') SEPARATOR '') as ac3_2,
       GROUP_CONCAT(IF(uid=2,acceptable_4,'') SEPARATOR '') as ac4_2
from user_answers ua
where importance <> 1 and uid in (1, 2)
group by ua.quid
having sum(uid = 1) > 0 and
       sum(uid = 2) > 0

由于无法在 WHERE 子句(ac1_1 等)中使用我用来容纳每个用户可接受的答案的别名，因此我尚未成功比较所有变量以获得预期结果。

任何指导表示赞赏。

Answer 1

您需要这样的东西(使用SELF JOIN):

SELECT t1.quid, t1.uid As u1, t2.uid As u2
FROM user_answers t1
JOIN user_answers t2 ON t1.uid > t2.uid AND t1.quid = t2.quid AND
     (FIELD(t1.answer, t2.acceptable_1, t2.acceptable_2, t2.acceptable_3, t2.acceptable_4) = 0  OR
      FIELD(t2.answer, t1.acceptable_1, t1.acceptable_2, t1.acceptable_3, t1.acceptable_4) = 0 )
WHERE t1.importance <> 1 AND t2.importance <> 1 and t1.uid in (1, 2) AND t2.uid in (1, 2);

如您所见，问题与答案的比较是通过 FIELD 完成的功能。

这是your Fiddle with my code .