基于TableB的Datetime与Partid过滤TableA数据的SQL需求

TableB 结构与数据 #

TableA 结构与数据 #

需求说明 #

根据TableB的条件过滤TableA数据：仅排除TableB中Datetime在2022-12-22T15:22:00.782Z至2022-12-24T15:20:00.782Z区间内的Part-A、Part-B对应的TableA数据，最终获取剩余数据的Customer ID列表（保留完整行信息）。

解决方案 #

以下是两种可实现需求的SQL写法：

方法1：NOT EXISTS子查询 #

SELECT a.Datetime, a.Partid, a.`Customer ID`
FROM TableA a
WHERE NOT EXISTS (
    SELECT 1
    FROM TableB b
    WHERE b.Partid IN ('Part-A', 'Part-B')
      AND b.Datetime BETWEEN '2022-12-22T15:22:00.782Z' AND '2022-12-24T15:20:00.782Z'
      AND a.Datetime = b.Datetime
      AND a.Partid = b.Partid
);

方法2：LEFT JOIN过滤 #

SELECT a.Datetime, a.Partid, a.`Customer ID`
FROM TableA a
LEFT JOIN (
    SELECT Datetime, Partid
    FROM TableB
    WHERE Partid IN ('Part-A', 'Part-B')
      AND Datetime BETWEEN '2022-12-22T15:22:00.782Z' AND '2022-12-24T15:20:00.782Z'
) b ON a.Datetime = b.Datetime AND a.Partid = b.Partid
WHERE b.Datetime IS NULL;

期望输出 #

内容的提问来源于stack exchange，提问作者Abhishek Kumar