筛选SQL WHERE子句

Question

我正试图找出在WHERE子句中放入什么，该子句将返回m.senderid、m.sender部门中NOT的m.receiverid的计数以及m.sender所在的部门名称。
例如，如果m.senderid在"Sales"中，则m.receiverid的计数将来自除"Sales"之外的所有部门
我正在尝试查找具有最高部门外通信的m.senderID的列表
(使用Google BigQuery(

SELECT DISTINCT m.sender, COUNT(DISTINCT m.receiver) AS messages_received, e.department
FROM collaboration.messages as m
JOIN collaboration.employees as e
ON m.sender = e.id
WHERE ?
GROUP BY m.sender, e.department
ORDER BY messages_received DESC
LIMIT 5;

发送和接收的消息
发件人	表示发送邮件的员工的员工id
receiver	表示接收消息的员工的员工id

如果我理解正确，这可能是您需要的：

SELECT DISTINCT m.sender, COUNT(DISTINCT m.receiver) AS messages_received, e.department
FROM collaboration.messages as m
JOIN collaboration.employees as e
ON m.sender = e.id
LEFT JOIN collaboration.employees as receiver_dep
ON m.receiver = receiver_dep.id 
WHERE receiver_dep.department <> e.department
GROUP BY m.sender, e.department
ORDER BY messages_received DESC
LIMIT 5;

你需要一个额外的加入来获得接收者的部门，并检查它是否与发送者的不同。