一尘不染

在多个子条件下查询父表

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 循环”)。


阅读 82

收藏
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