在多个子条件下查询父表

小编典典

在多个子条件下查询父表

sql

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

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

父表是：

邮件：

IdEmail	Subject	Other…
1	…	…
2	…	…
3	…	…
4	…	…
5	…	…
etc.	…	…

和子表

邮件标签


Id	IdTag	IdMail
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
etc.

我想在两种一般情况下按标签查询电子邮件：

场景 1：（标签白名单 9、11）
选择所有IdTag =9 和 IdTag=11 的电子邮件。返回电子邮件 3、5 和 6。

场景 2：（标签白名单 9、11 和黑名单 10、12）
选择所有IdTag =9 和 IdTag=11但没有任何IdTag=10 或 IdTag=12 的电子邮件。仅返回电子邮件 6。

白/黑名单可以包含 0 个或多个 IdTag。编辑：这部分必须参数化。

如何为这两种情况编写查询？如果可能，最好避免任何类型的循环（“while 循环”）。

阅读 102

2022-07-22

共1个答案

小编典典

正如我所提到的，在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;

根据新的目标帖子，这可能有效，但是，它不会返回电子邮件 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;

2022-07-22