在单个MySQL表中进行卷积比较和排除 [英] Convoluted comparisons and exclusions inside a single MySQL table

问题描述

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

小提琴包含与我以下示例一致的数据。

我有一张桌子我正在努力工作：

表1：user_answers表（存储用户答案及其可接受的各种问题的答案）

存储的值得注意的是：

用户ID（列 uid ）

问题ID（列 quid ）

答案（列回答）

可接受的答案存储＃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 和 acceptable_2 ，值为0。

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

示例1：

用户1在quid 1上回答3，并从同一个问题的另一个用户接受答案2和4。

用户2在quid 1上回答2，接受相同问题的另一位用户的答案3

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

示例2：
用户1在quid 2上回答2并接受答案1或者从另一个用户那里获得同样的问题。

用户2在quid 2上回答2，并接受来自另一个用户的答案2或4。 >

示例2的结果
用户1不接受用户2的答案，但用户2将接受用户1的答案。

示例3：
用户1答案1 on quid 3，并接受来自另一个用户的同一问题的答案1或2。

用户2在quid 3上回答1，并接受来自另一个用户的答案1或2

示例3的结果
用户1将接受用户2的答案，用户2将接受用户1的答案。没有冲突，这个结果应该从结果中排除。

示例4：
用户1在quid 4上回答1并且将接受来自另一个用户的答案1或2。

用户2在quid 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 =作为a2，
 GROUP_CONCAT（IF（uid = 1，acceptable_1，''）SEPARATOR''）为ac1_1，
 GROUP_CONCAT（IF（uid = 1，acceptable_2， as）as ac2_1，
 GROUP_CONCAT（IF（uid = 1，acceptable_3，''）SEPARATOR'）as ac3_1，
 GROUP_CONCAT（IF（uid = 1，acceptable_4，'' ）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 important<> 1和uid in（1，2）
 group by ua.quid 
 have sum（uid = 1）> 0和
 sum（uid = 2）> 0 
  

无法使用我用来为我的WHERE中的每个用户提供可接受的答案的别名条款（ac1_1等），我还没有成功比较所有的变量来获得预期的结果。

任何指导赞赏。

解决方案

你需要这样的东西（使用 SELF JOIN ）： / p>

  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,2）和t2.uid（1，2）中的1和t1.uid; 
  

如你所见，问题答案比较是用 FIELD 功能。 p>

以下是您的小提琴与我的代码。

MySQLfiddle here: http://sqlfiddle.com/#!2/6094e8/6

The fiddle contains the data that goes along with my examples below.

I have a single table I am trying to work with:

Table 1: user_answers table (stores users answers and their acceptable asnwers to various questions)

The notable values that are stored are:

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)

The answer column will store a value between 1 and 4.

The acceptable columns hold selected acceptable answers which are in line with their column placement. For example, user 1 answers 1, but would accept answers 2 and 3. This would make acceptable_2 = 2 and acceptable_3 = 3, leaving acceptable_1 and acceptable_2 with a value of 0.

For the sake of example I'll try and explain the results I am looking for:

Example 1:
User 1 answers 3 on quid 1 and would accept answers 2 and 4 from another user for that same question.

User 2 answers 2 on quid 1 and would accept answers 3 from another user for that same question

Result for Example 1: User 1 would accept user 2's answer and user 2 would accept accept user 1's answer. This result should be excluded from the results.

Example 2: User 1 answers 2 on quid 2 and would accept answers 1 or 4 from another user for that same question.

User 2 answers 2 on quid 2 and would accept answers 2 or 4 from another user for that same question.

Result for Example 2: User 1 would not accept user 2's answer, but user 2 would accept user 1's answer. This comparison has a single conflict in it and should be included in the results.

Example 3: User 1 answers 1 on quid 3 and would accept answers 1 or 2 from another user for that same question.

User 2 answers 1 on quid 3 and would accept answers 1 or 2 from another user for that same question.

Result for Example 3: User 1 would accept user 2's answer and user 2 would accept user 1's answer. There is no conflict and this result should be excluded from the results.

Example 4: User 1 answers 1 on quid 4 and would accept answers 1 or 2 from another user for that same question.

User 2 answers 3 on quid 4 and would accept answers 3 or 4 from another user for that same question.

Result for Example 4: User 1 would not accept user 2's answer and user 2 would not accept user 1's answer. There are two conflicts and this result should be included in the results.

As I hope is visible, I would like to grab the quid for the questions both users have answered which have conflict in their answers in terms of what is in the other users acceptable columns.

So far I have been able to gather the answers and acceptable answers for each user using this query:

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

Not being able to use the aliases I use to house the acceptable answers for each user in my WHERE clause (ac1_1, etc), I have yet to successfully compare all the variables to get the expected results.

Any guidance appreciated.

解决方案

You need something like this (using 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);

As you can see, question to answers comparison is done with FIELD function.

Here's your Fiddle with my code.

这篇关于在单个MySQL表中进行卷积比较和排除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！