MySQL自连接表并比较结果

Question

MySQLFiddle链接: http://sqlfiddle.com/#!2/15d447/1

我有一个单表需要操作:

表1: user_answers（存储用户对各种问题的答案）

存储的值包括用户 ID (列uid)、他们正在回答的问题的问题 ID (列quid)、问题的答案 (列answer) 和答案的重要性 (列importance)。

我想要的最终结果:

我希望能够获取任何两个用户都回答过的所有问题，排除任何一方没有回答或者同一问题两个用户在列importance中的任意一个值为1的情况。同样，这将仅用于比较两个用户。

我一直没有成功的尝试，但以下是我尝试的内容:

#attempt one: trying to exclude answers that were not answered by both users
SELECT * FROM user_answers AS uid1
JOIN user_answers AS uid2 ON uid1.uid = uid2.uid
WHERE uid1.uid = 1
AND uid2.uid = 20008
AND uid1.quid IS NOT NULL
AND uid2.quid IS NOT NULL;

这个没有返回结果，但我不太确定原因。

#attempt two: trying to exclude where answers are the same for both users
SELECT * FROM user_answers AS uid1
LEFT JOIN user_answers AS uid2 ON (uid1.uid = uid2.uid AND uid1.answer <> uid2.answer)

这给我带来了结果，但由于连接的关系似乎重复了一切。在这个尝试中，我也试图消除任何相同的答案，从那个意义上看似乎是有效的。 感谢您提供任何指导。

- user1026996

---

怎么能不给一个以“My SQL Fiddle is here”开头的问题点赞呢？ - Gordon Linoff

Answer 1

你可以使用聚合查询来回答你的问题。思路是使用having子句过滤出符合你所寻找条件的行。

由于你完全不关心importance = 1的问题，所以需要用where子句进行过滤：

select ua.quid
from user_answers ua
where importance <> 1 and uid in (1, 20008)
group by ua.quid
having sum(uid = 1) > 0 and
       sum(uid = 20008) > 0;

如果你想包含答案，可以这样做：

select ua.quid,
       group_concat(concat(uid, ':', answer) order by uid) as answers

- Gordon Linoff

---

避免使用JOIN的好方法。不过，我个人更喜欢将双方的答案作为单独的列呈现，GROUP_CONCAT(IF(uid=1,answer,'') SEPARATOR '') as a1, GROUP_CONCAT(IF(uid=1,answer,'') SEPARATOR '') as a2。 - Wrikken

哦，顺便说一下，对于两个用户，可以使用COUNT(distinct uid) = 2而不是两个SUM。 - Wrikken

@cjaredrun...在select语句中，quid后面缺少一个逗号。 - Gordon Linoff

@GordonLinoff 有点尴尬，我错过了这个问题，但在对更大的数据集运行查询后，我意识到我没有排除两个用户回答相同的结果。您可以在此处查看：http://sqlfiddle.com/#!2/15d447/25，其中 quid 2 的答案是相同的。我尝试将 WHERE a1<>a2 添加到 group_concat 的同一行，以及在初始 WHERE 后添加 AND a1<>a2，但未能使其正常工作。有什么建议吗？谢谢。 - user1026996

@cjaredrun...我建议你提出另一个问题。 - Gordon Linoff

新问题在这里：http://stackoverflow.com/questions/20695368/mysql-comparing-and-excluding-results-properly - user1026996

Answer 2

你需要的只是一个简单的版本。

select *
 from user_answers a,
      user_answers b
where a.quid = b.quid
  and a.uid <> b.uid
  and 1 not in (a.importance, b.importance)

如果您想过滤问题，请将*更改为distinct a.quid

在这里查看fiddle： http://sqlfiddle.com/#!2/15d447/15

- Jorge Campos