php - MySQL 查询选择两个用户之间的冲突计数

Question

sqlfiddle在这里:http://sqlfiddle.com/#!2/bde34

fiddle 数据包含:

用户 1 和用户 2 之间的冲突问题总数:3
用户 1 与用户 2 发生冲突:2
用户 2 与用户 1 发生冲突:2

我有一个查询，选择回答了同一问题的用户之间的冲突总数。我想要的是让它显示用户 1 与用户 2 以及用户 2 与用户 1 的个人冲突计数

供引用:

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

存储的值得注意的值是:

users id (column uid)
question id for the question they are answering (column quid)
answer to the question (column answer)
acceptable answer storage #1 stores other acceptable answers for that user (column acceptable_1)
acceptable answer storage #2 stores other acceptable answers for that user (column acceptable_2)
acceptable answer storage #3 stores other acceptable answers for that user (column acceptable_3)
acceptable answer storage #4 stores other acceptable answers for that user (column acceptable_4)
importance of their answer (column importance)

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

可接受的列包含与其列位置一致的选定可接受答案。例如，用户 1 回答 1，但会接受答案 2 和 3。这将使 acceptable_2 = 2 且acceptable_3 = 3，使acceptable_1 和acceptable_2 的值为0。当比较两个用户并且其中一个用户输入了答案而另一个用户没有输入可接受的值时，就会发生冲突。

现在我有一个查询可以获取两个用户之间的冲突总数，但我在获取单独的计数时遇到了困难。例如，与用户 2 相比，用户 1 具体有多少冲突，反之亦然。

例如:

#select counts of BOTH users total conflicts
SELECT COUNT(*)
    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)

此查询为我提供了用户 1 和用户 2 之间的总冲突，但我无法获得单独的比较计数。我尝试删除其中一个 FIELD 函数来尝试一次仅比较一个用户，但结果不是我所期望的。

感谢任何帮助。

Answer 1

试试这个

select sum(case(FIELD(t1.answer, t2.acceptable_1, t2.acceptable_2, t2.acceptable_3,     t2.acceptable_4)) when 0 then 1 else 0 end) as conflict_1_over_2,
sum(case(FIELD(t2.answer, t1.acceptable_1, t1.acceptable_2, t1.acceptable_3, t1.acceptable_4)) when 0 then 1 else 0 end) as conflict_2_over_1
     FROM user_answers t1
JOIN user_answers t2 ON t1.uid > t2.uid AND t1.quid = t2.quid
    WHERE t1.importance <> 1 AND t2.importance <> 1 and t1.uid in (1, 2) AND t2.uid in (1, 2)