查询多个子条件的父表

Question

我有两个表(Mail和MailTag)，每个表包含超过500,000条记录，因此查询效率非常重要。

对于每个电子邮件记录(Mail)，在MailTag子表中至少记录了一个标签。每封邮件都可以有多个标签(一个或多个)。

父表是:

邮件:

等。…

如前所述，在HAVING中使用条件聚合。如果只需要这些标签，则使用以下命令:

SELECT M.IdEmail,
M.Subject,
M.[Other...]
FROM dbo.Mail
JOIN dbo.MailTag MT ON M.IdMail = MT.IdMail
GROUP BY M.IdEmail,
M.Subject,
M.[Other...]
HAVING COUNT(CASE WHEN MT.idTag = 9 THEN 1 END) > 0
AND COUNT(CASE WHEN MT.idTag = 11 THEN 1 END) > 0;

对于黑名单，则同样适用，但您希望0在这种情况下:

SELECT M.IdEmail,
M.Subject,
M.[Other...]
FROM dbo.Mail
JOIN dbo.MailTag MT ON M.IdMail = MT.IdMail
GROUP BY M.IdEmail,
M.Subject,
M.[Other...]
HAVING COUNT(CASE WHEN MT.idTag = 9 THEN 1 END) > 0
AND COUNT(CASE WHEN MT.idTag = 11 THEN 1 END) > 0
AND COUNT(CASE WHEN MT.idTag IN (10,12) THEN 1 END) = 0;

---

根据新的Goal Posts，这可能工作，但是，它不返回Email 6，因为在您的示例数据中不存在这样的ID:

USE Sandbox;
GO
DECLARE @Whitelist AS table (ID int UNIQUE);
INSERT INTO @Whitelist (ID)
VALUES(9),(11);
DECLARE @Blacklist AS table (ID int UNIQUE);
INSERT INTO @Blacklist (ID)
VALUES(10),(12);
WITH Mail AS(
SELECT *
FROM (VALUES(1,'...','...'),
(2,'...','...'),
(3,'...','...'),
(4,'...','...'),
(5,'...','...'))V(IdEmail,Subject,Other)),
MailTag AS (
SELECT *
FROM (VALUES(1,9,1),
(2,9,2),
(3,9,3),
(4,10,2),
(5,10,3),
(6,11,3),
(7,12,3),
(8,9,5),
(9,10,5),
(10,11,5),
(11,12,5),
(12,9,6),
(13,11,6),
(14,13,6))V(Id,IdTag,IdMail))
SELECT M.IdEmail,
M.Subject,
M.Other
FROM Mail M
JOIN MailTag MT ON M.IdEMail = MT.IdMail --Why is thuis called IdEmail in one table, and IdMail in the other table?
LEFT JOIN @Whitelist WL ON MT.IdTag = WL.ID
LEFT JOIN @Blacklist BL ON MT.IdTag = BL.ID
GROUP BY M.IdEmail,
M.Subject,
M.Other
HAVING COUNT(DISTINCT WL.ID) = (SELECT COUNT(ID) FROM @Whitelist)
AND COUNT(BL.ID) = 0;