mysql - 选择没有其他行匹配的行

Question

开始时这似乎很简单，但它变得很尴尬。

假设我们有一个表包含...

+---------+-----------+
| chat_id | friend_id |
+---------+-----------+
| A       |         1 |
| A       |         2 |
| A       |         3 |
| B       |         1 |
| B       |         2 |
| C       |         1 |
| C       |         2 |
| C       |         3 |
| D       |         1 |
| D       |         2 |
| D       |         3 |
| D       |         4 |
| D       |         5 |
| E       |         0 |
| E       |         1 |
| E       |         2 |
| E       |         3 |
| E       |         4 |
| E       |         5 |
| E       |         6 |
| E       |         7 |
| F       |         0 |
| F       |         1 |
| G       |         1 |
| G       |         2 |
+---------+-----------+

我希望只选择那些具有 friend_ids 1 和 2 而没有其他 friend_id 的 chat_id，返回 B 和 G 的 SQL 是什么？

到目前为止，我想到的最好的是:

SELECT DISTINCT a.chat_id, COUNT(*) 
FROM tt2 a 
LEFT JOIN tt2 b 
ON a.chat_id = b.chat_id 
AND b.friend_id NOT IN (1,2) 
WHERE a.friend_id in (1,2) 
and b.chat_id IS NULL GROUP BY a.chat_id HAVING COUNT(*) = 2;

+---------+----------+
| chat_id | count(*) |
+---------+----------+
| B       |        2 |
| G       |        2 |
+---------+----------+
2 rows in set (0.00 sec)

以防万一我正在寻找只有 1,2,3 存在的 chat_id...

SELECT DISTINCT a.chat_id, COUNT(*) 
FROM tt2 a 
LEFT JOIN tt2 b 
ON a.chat_id = b.chat_id 
AND b.friend_id not in (1,2,3) 
WHERE a.friend_id IN (1,2,3) 
AND b.chat_id IS NULL 
GROUP BY a.chat_id 
HAVING COUNT (*) = 3;

+---------+----------+
| chat_id | count(*) |
+---------+----------+
| A       |        3 |
| C       |        3 |
+---------+----------+

但是这个表可能会变得很大，我需要快速的 SQL，有没有人知道更好的方法？

尝试澄清...我得到了一堆 friend_id，我想获得 chat_id，其中只有那些 friend_id 存在于那个 chat_id ....SQL 很快(在 sqlite 上)

非常感谢!

Answer 1

这里有一个选项应该能够限制所需的数据量

SELECT 
    d.chat_id,
    COUNT(DISTINCT s.friend_id) AS matchedFriends,
    COUNT(DISTINCT d.friend_id) AS totalFriends
FROM tt2 AS d
INNER JOIN tt2 AS s
    ON s.chat_id = d.chat_id
    AND s.friend_id IN (1,2)
GROUP BY d.chat_id
HAVING matchedFriends = 2
AND totalFriends = matchedFriends

INNER JOIN s 确保它只命中至少有一个请求的 friend 的行。matchedFriends 计数检查找到了多少请求的 friend 。

然后，totalFriends 计数会检查该聊天中总共有多少 friend 。

最后HAVING首先确定有2个匹配的好友，然后检查好友总数是否等于匹配的好友数。

这将要求您同时提供 friend 列表和您正在寻找的 friend 的数量，但应该是高效的。

为了提高效率，在 (chat_id,friend_id) 上建立索引(如果您还没有，假设在撰写本文时它是一个由两部分组成的 PK)