mysql - 连接两个表并选择第二个表中不存在的行 1

Question

我有一个名为 users 的表，其中有一个名为 Bob 的用户，如下所示:

username
  Bob

我有另一个名为 mail 的表，其中包含以下行:

sender | receiver
Marley | Bob
 Saget | Bob 

我有另一个名为 block 的表，其中包含以下行:

blocker | blocked
 Marley | Bob

我想从表用户中选择用户以及他们收到的消息数，但忽略来自被阻止用户的消息。所以结果应该是这样的:

users.username | count_mail 
 Bob           |  1 

因此，正如您所看到的，我需要获取 Bob 以及他收到的消息数，自从 Bob 阻止了 Marley 以来，该消息数仅为 1，因此 Saget 的消息应该是唯一计数的 1。

我使用以下查询，但它什么也没返回:

SELECT user, COUNT(mail) AS count_mail FROM users 
LEFT JOIN block ON (block.blocker = users.username) 
LEFT JOIN mail ON (mail.receiver = users.username 
                   AND mail.sender <> block.blocked)

谢谢

Answer 1

试试这个:

SELECT users.username, COUNT(mail.receiver) AS count_mail
FROM users 
LEFT JOIN mail ON mail.receiver = users.username 
LEFT JOIN block ON block.blocker = users.username
    AND mail.sender = block.blocked
WHERE block.blocked IS NULL
GROUP BY 1

这是有效的，因为它仅在阻止程序的左连接失败时才会命中 - 由 where 子句尝试非连接断言。