mysql - 在表格中找到过去几周没有事件的记录

我正在尝试在一个表中查找过去几周没有活动的记录（工作订单）。我需要从表中选择不同的工作订单，其中日期字段不在当前日期的过去两周之内。

我可以得到日期早于过去两周的工单。

select distinct WorkOrder
from SMWorkOrder
where SMCo = 1 and WorkOrder in
           (select WorkOrder
            from SMWorkCompleted
            where SMCo = 1 and Date <  DATEADD(WEEK, -2, GETDATE()))

如何获得该表中包含行的日期早于过去两周并且在过去两周内也没有行的工单？

最佳答案

在以下情况下，使用左外部联接确实非常有效：

SELECT
    o.WorkOrder
FROM
    SMWorkOrder o
    LEFT JOIN SMWorkCompleted c
    ON o.WorkOrder = c.WorkOrder
    AND c.Date >= DATEADD(WEEK,-2,CAST(GETDATE() AS DATE)) AND c.Date <= GETDATE()
    AND c.SMCo = 1
WHERE
    c.WorkOrder is NULL
    AND o.SMCo = 1

但是请注意，我怀疑您可能在两个表上都有SMCo，但如果不在SWorkOder表上，则应将其从where条件中删除；如果在SWorkCompleted表上，则应将其从联接的ON条件中删除。

对于IN方法，您实际上要使用NOT IN：

SELECT
    o.WorkOrder
FROM
    SWorkOrder o
WHERE
    o.SMCo = 1
    AND o.WorkOrder NOT IN (
       SELECT
          WorkOrder
       FROM
          SMWorkCompleted c
       WHERE
          c.Date >= DATEADD(WEEK,-2,CAST(GETDATE() AS DATE)) AND c.Date <= GETDATE()
          AND c.SMCo = 1
    )

注意如果WorkOrder可以为NULL，请不要使用IN！

NOT EXISTS方法也可以很好地工作：

SELECT
    o.WorkOrder
FROM
    SWorkOrder o
WHERE
    o.SMCo = 1
    AND NOT EXISTS (
       SELECT
          WorkOrder
       FROM
          SMWorkCompleted c
       WHERE
          c.WorkOrder = o.WorkOrder
          AND c.Date >= DATEADD(WEEK,-2,CAST(GETDATE() AS DATE)) AND c.Date <= GETDATE()
          AND c.SMCo = 1
    )