查找一列中具有相同值而另一列中具有其他值的行？

Ray 发表于 Dev

射线

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

# 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之间存在对话？

我尝试了以下方法：

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子句中。

我究竟做错了什么？

欧文·布兰德斯特

这是关系分裂的情况。在这个相关的问题下，我们组建了一个技术库：

如何筛选具有多次通过关系的SQL结果

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

选择其他表中不存在的行

我建议LEFT JOIN/ IS NULL：

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;

或NOT EXISTS：

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。
唯一的条件是您制定的条件：

...一个查询，表明仅用户32和用户3之间存在对话？

审计查询

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

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)
   );

与其他两个查询类似，不同之处在于如果user_id = 3多次链接，它将不会返回多行。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。

编辑于2021-02-17

我来说两句

0条评论

登录后参与评论

来自分类Dev

Related 相关文章

文章