连接两个表，并选择在第二1

问题描述：

我有一个名为用户与被叫鲍勃这样的用户表中不存在的行：连接两个表，并选择在第二1

username 
    Bob

我已经另一个表称为邮件用下列行：

sender | receiver 
Marley | Bob 
Saget | Bob

我叫块具有以下行另一个表：

blocker | blocked 
Marley | Bob

我要选择用户来回在表用户和他们收到的消息的数量，但忽略来自被阻止的用户的消息。所以结果应该是这样的：

users.username | count_mail 
Bob   | 1

因此，大家可以看到我需要与他收到该邮件的数量相处鲍勃只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)

感谢

答

试试这个：

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子句尝试为非连接断言。

答

试试这个

SELECT user, COUNT(mail) AS count_mail FROM users 
LEFT JOIN mail ON (mail.receiver = users.username) 
WHERE (mail.sender is null 
     or NOT EXISTS 
      (SELECT 1 FROM block 
       WHERE mail.sender = block.blocker 
       AND mail.receiver = block.blocked) 
    )

SELECT user, COUNT(mail) AS count_mail FROM users 
LEFT JOIN (SELECT * FROM mail WHERE NOT EXISTS 
       (SELECT 1 FROM block 
        WHERE mail.sender = block.blocker 
        AND mail.receiver = block.blocked) 
     ) mail ON (mail.receiver = users.username)

连接两个表，并选择在第二1

相关推荐