查找一列中具有相同值而另一列中具有其他值的行？ [英] Find rows that have same value in one column and other values in another column?

问题描述

我有一个PostgreSQL数据库，将用户存储在 users 表中，并将他们参与的对话存储在 conversation 表中。由于每个用户可以参与多个对话，并且每个对话可以涉及多个用户，因此我有一个 conversation_user 链接表来跟踪哪些用户正在参与每个对话：

I have a PostgreSQL database that stores users in a users table and conversations they take part in a conversation table. Since each user can take part in multiple conversations and each conversation can involve multiple users, I have a conversation_user linking table to track which users are participating in each conversation:

# conversation_user
id  |  conversation_id | user_id
----+------------------+--------
1   |                1 |      32
2   |                1 |       3
3   |                2 |      32
4   |                2 |       3
5   |                2 |       4

在上表中，用户32仅与用户3进行一次对话，而与用户3进行另一次对话和用户4。我该如何写一个查询来显示仅用户32和用户3之间存在对话？

In the above table, user 32 is having one conversation with just user 3 and another with both 3 and user 4. How would I write a query that would show that there is a conversation between just user 32 and user 3?

我尝试了以下操作：

SELECT conversation_id AS cid,
       user_id
FROM conversation_user
GROUP BY cid HAVING count(*) = 2
AND (user_id = 32
     OR user_id = 3);

SELECT conversation_id AS cid,
   user_id
FROM conversation_user
GROUP BY (cid HAVING count(*) = 2
AND (user_id = 32
     OR user_id = 3));

SELECT conversation_id AS cid,
       user_id
FROM conversation_user
WHERE (user_id = 32)
  OR (user_id = 3)
GROUP BY cid HAVING count(*) = 2;

这些查询会引发错误，指出user_id必须出现在 GROUP BY中子句或在聚合函数中使用。将它们放在汇总函数中（例如 MIN 或 MAX ）听起来不太合适。我以为我的前两次尝试是将它们放在 GROUP BY 子句中。

These queries throw an error that says that user_id must appear in the GROUP BY clause or be used in an aggregate function. Putting them in an aggregate function (e.g. MIN or MAX) doesn't sound appropriate. I thought that my first two attempts were putting them in the GROUP BY clause.

我在做什么错了？

推荐答案

关系划分。在这个相关的问题下，我们组装了一系列技术：

This is a case of relational division. We have assembled an arsenal of techniques under this related question:

• 如何过滤具有has-many-through关系的SQL结果

• How to filter SQL results in a has-many-through relation

特殊的困难是排除其他用户。基本上有4种技术。

The special difficulty is to exclude additional users. There are basically 4 techniques.

• 选择其他表中不存在的行

• Select rows which are not present in other table

左联接 / IS空 c：

SELECT cu1.conversation_id
FROM        conversation_user cu1
JOIN        conversation_user cu2 USING (conversation_id)
LEFT   JOIN conversation_user cu3 ON cu3.conversation_id = cu1.conversation_id
                                 AND cu3.user_id NOT IN (3,32)
WHERE  cu1.user_id = 32
AND    cu2.user_id = 3
AND    cu3.conversation_id IS NULL;

或不存在：

SELECT cu1.conversation_id
FROM   conversation_user cu1
JOIN   conversation_user cu2 USING (conversation_id)
WHERE  cu1.user_id = 32
AND    cu2.user_id = 3
AND NOT EXISTS (
   SELECT 1
   FROM   conversation_user cu3
   WHERE  cu3.conversation_id = cu1.conversation_id
   AND    cu3.user_id NOT IN (3,32)
   );

两个查询都不依赖于 UNIQUE （conversation_id，user_id）的约束，该约束可能存在也可能没有。这意味着，对于同一对话，如果多次列出 user_id 32（或3），该查询甚至可以工作。不过，您将在结果中得到重复的行，并且需要应用 DISTINCT 或 GROUP BY 。

唯一的条件是您制定的条件：

Both queries do not depend on a UNIQUE constraint for (conversation_id, user_id), which may or may not be in place. Meaning, the query even works if user_id 32 (or 3) is listed more than once for the same conversation. You would get duplicate rows in the result, though, and need to apply DISTINCT or GROUP BY.
The only condition is the one you formulated:

...一条查询将显示仅在用户32和用户3之间存在对话？

... a query that would show that there is a conversation between just user 32 and user 3?

经审核的查询

您在评论中链接的查询不起作用。您忘记了排除其他参与者。应该是这样的：

Audited query

The query you linked in the comment wouldn't work. You forgot to exclude other participants. Should be something like:

SELECT *  -- or whatever you want to return
FROM   conversation_user cu1
WHERE  cu1.user_id = 32
AND    EXISTS (
   SELECT 1
   FROM   conversation_user cu2
   WHERE  cu2.conversation_id = cu1.conversation_id 
   AND    cu2.user_id = 3
   )
AND NOT EXISTS (
   SELECT 1
   FROM   conversation_user cu3
   WHERE  cu3.conversation_id = cu1.conversation_id
   AND    cu3.user_id NOT IN (3,32)
   );

与其他两个查询类似，但是如果<$ c不会返回多行$ c> user_id = 3 已链接多次。

Which is similar to the other two queries, except that it will not return multiple rows if user_id = 3 is linked multiple times.

这篇关于查找一列中具有相同值而另一列中具有其他值的行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！