MySQL正确比较和排除结果 [英] MySQL comparing and excluding results properly

问题描述

MySQLFiddle此处： http://sqlfiddle.com/#!2/15d447/25

MySQLFiddle here: http://sqlfiddle.com/#!2/15d447/25

我有一个要使用的表格：

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

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

Table 1: user_answers table (stores users answers to various questions)

存储的值得注意的值是用户ID（列 uid ），他们正在回答的问题的问题ID（列 quid ），问题的答案（列 answer ）和他们答案的重要性（列重要性）。

The notable values that are stored are the users id (column uid), the question id for the question they are answering (column quid), the answer to the question (column answer) and the importance of their answer (column importance).

我想要的最终结果：

我希望能够抓住任何两个用户已回答的所有问题，从对方未回答的问题中排除任何答案，而答案之间必须相同两个或两个相同问题的答案，对于每个用户，重要性为1。同样，这一次只能用于一次比较两个用户。

I'd like to be able to grab all the questions that any two users have answered, excluding any answers from questions that have either not been answered by the other party, answers that are identical between the two, or answers to the same question which have a value of 1 for either user in importance. Again, this will only ever be used to compare two users at a time.

到目前为止，我已经能够获得结果，并为两个用户提供答案。除了排除答案外，我的条件都相同。在sqlfiddle示例中，您将看到两个用户都回答quid 2，答案为 aa。所需的结果将排除quid 2，而仅返回quid 1。

Thus far I have been able to get the results, and answers for the two users with all of my conditions except excluding answers they have both answered the same. In the sqlfiddle example you will see both users answered quid 2 with the answer 'aa'. The desired result would exclude quid 2 and only return quid 1.

我当前使用的查询是：

select ua.quid,
GROUP_CONCAT(IF(uid=1,answer,'') SEPARATOR '') as a1, GROUP_CONCAT(IF(uid=20008,answer,'') SEPARATOR '') as a2
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;

通过此查询，我尝试添加 WHERE a1<> a2 与group_concat在同一行以及 AND a1<>最初 WHERE 之后的a2 ，但无法使其正常工作。

With this query I attempted to add WHERE a1 <> a2 on the same line as the group_concat as well as AND a1 <> a2 after the initial WHERE but was unable to get that to work.

有什么建议吗？

推荐答案

现在我想我明白了。

如果用户对每个问题只有一个答案，则应该可以：

If the users have only one answer for each question, then this should work:

select ua.quid,
       GROUP_CONCAT(IF(uid=1,answer,'') SEPARATOR '') as a1,
       GROUP_CONCAT(IF(uid=20008,answer,'') SEPARATOR '') as a2
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 and
       max(case when uid = 1 then answer end) <> max(case when uid = 20008 then answer end);

编辑：

当然，用户可以有多个答案。这促使我朝着 join 方法而不是仅聚合的方法发展。不幸的是，这种方法需要完全外部联接，MySQL不支持。因此，让我们首先尝试通过答案进行汇总。您只有两个用户，因此我们将调整值：

Of course, users can have multiple answers. This is pushing me toward a join approach rather than an aggregate-only approach. Unfortunately, such an approach needs a full outer join, which MySQL does not support. So, let's try aggregating by answer, first. You only have two users, so we'll pivot the values:

select ua.quid,
       GROUP_CONCAT(IF(user1 > 0, answer, '') SEPARATOR '') as a1,
       GROUP_CONCAT(IF(user20008 > 0, answer, '') SEPARATOR '') as a2
from (select ua.quid, ua.answer,
             max(case when ua.uid = 1 then 1 else 0 end) as user1,
             max(case when ua.uid = 20008 then 1 else 0 end) as user20008
      from user_answers ua
      where importance <> 1 and ua.uid in (1, 20008)
      group by ua.quid, ua.answer
     ) t
where t.user1 = 0 or t.user20008 = 0
group by ua.quid;

这篇关于MySQL正确比较和排除结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！